SQL 101

SQL (Structured Query Language) is the standard language for working with relational databases. Every major database system—PostgreSQL, MySQL, SQLite, Supabase, BigQuery, Snowflake—speaks SQL. This course teaches you everything you need to go from zero to writing real, production-quality queries.

What you will learn

  1. Databases & Tables — how data is organized
  2. SELECT Basics — reading data
  3. Filtering with WHERE — narrowing results
  4. Sorting & Limiting — ordering and paging
  5. Aggregations & GROUP BY — summarizing data
  6. JOINs — combining multiple tables
  7. Subqueries & CTEs — nested and reusable queries
  8. Modifying Data — INSERT, UPDATE, DELETE
  9. Creating & Altering Tables — DDL basics
  10. Window Functions — advanced analytics

Running SQL

  • Online (free, instant): sqliteonline.com — paste and run SQL in your browser
  • Supabase: project → SQL editor (PostgreSQL)
  • VS Code: SQLite extension + any .db file
  • PostgreSQL locally: psql in terminal

Sample tables used throughout this course

All examples below use these three tables. You can create them yourself using Lesson 9.

  • users -- id, name, email, age, city, joined_at
  • orders -- id, user_id, product, amount, status, created_at
  • products -- id, name, category, price, stock

1. Databases & Tables

1.1 What is a relational database?

A relational database stores data in tables — like spreadsheets, but with rules. Each table has:

  • Columns — the fields (e.g., name, email)
  • Rows — one record per row
  • Primary key — a column that uniquely identifies each row (usually id)
  • Foreign key — a column in one table that references the primary key of another

1.2 Data types you need to know

TypeDescriptionExample
INTEGER / INTWhole numbers42
NUMERIC / DECIMALPrecise decimal numbers9.99
TEXT / VARCHAR(n)Text strings'hello'
BOOLEANTrue / falsetrue
DATECalendar date'2025-01-15'
TIMESTAMPDate + time'2025-01-15 09:30:00'
NULLMissing / unknown valueNULL

1.3 Key concepts

  • Schema — the structure/blueprint of a database (its tables, columns, types, and relationships)
  • Query — a question you ask the database, written in SQL
  • Result set — the rows returned by a query
  • NULL — represents a missing or unknown value. NULL ≠ 0 and NULL ≠ ''
  • Case sensitivity — SQL keywords are case-insensitive (SELECT = select); string values are case-sensitive in most databases

2. SELECT Basics

SELECT is how you read data. It is the most-used SQL statement by far.

2.1 Select all columns

  • SELECT *
  • FROM users;

Returns every column and every row in users. The * means "all columns".

2.2 Select specific columns

  • SELECT name, email
  • FROM users;

2.3 Aliases with AS

Rename a column in the result set without changing the database.

  • SELECT name AS full_name,
  •      email AS contact_email
  • FROM users;

2.4 Calculated columns

  • SELECT name,
  •      price,
  •      price * 1.1 AS price_with_tax
  • FROM products;

2.5 Removing duplicates with DISTINCT

  • SELECT DISTINCT city
  • FROM users; -- returns each city once

2.6 String functions

  • SELECT UPPER(name) AS name_upper FROM users;
  • SELECT LOWER(email) AS email_lower FROM users;
  • SELECT LENGTH(name) AS name_length FROM users;
  • SELECT name || ' — ' || city AS label FROM users; -- concatenate (PostgreSQL)
  • SELECT CONCAT(name, ' — ', city) AS label FROM users; -- concatenate (MySQL)
  • SELECT TRIM(' hello ') AS trimmed; -- removes leading/trailing spaces

2.7 Number functions

  • SELECT ROUND(price, 2) FROM products; -- 2 decimal places
  • SELECT ABS(-5); -- absolute value → 5
  • SELECT CEIL(4.1); -- round up → 5
  • SELECT FLOOR(4.9); -- round down → 4

2.8 Handling NULL

  • SELECT COALESCE(phone, 'no phone') AS phone FROM users; -- returns first non-NULL
  • SELECT name FROM users WHERE phone IS NULL;
  • SELECT name FROM users WHERE phone IS NOT NULL;

3. Filtering with WHERE

WHERE limits which rows are returned. Think of it as "only include rows where this condition is true."

3.1 Comparison operators

OperatorMeaningExample
=Equal toage = 30
!= or <>Not equal tostatus != 'cancelled'
>Greater thanamount > 100
<Less thanage < 25
>=Greater than or equalprice >= 10
<=Less than or equalstock <= 5

3.2 AND / OR / NOT

  • SELECT * FROM users
  • WHERE age >= 21 AND city = 'New York';
  • SELECT * FROM orders
  • WHERE status = 'pending' OR status = 'processing';
  • SELECT * FROM products
  • WHERE NOT category = 'electronics';

Tip: use parentheses when mixing AND and OR to make precedence explicit:

  • WHERE (status = 'pending' OR status = 'processing')
  •   AND amount > 50;

3.3 IN

Match any value in a list — cleaner than multiple OR conditions.

  • SELECT * FROM users
  • WHERE city IN ('New York', 'Los Angeles', 'Chicago');
  • SELECT * FROM orders
  • WHERE status NOT IN ('cancelled', 'refunded');

3.4 BETWEEN

Inclusive range filter.

  • SELECT * FROM products
  • WHERE price BETWEEN 10 AND 50;
  • SELECT * FROM orders
  • WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

3.5 LIKE — pattern matching

  • % — matches any number of characters
  • _ — matches exactly one character
  • SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with @gmail.com
  • SELECT * FROM users WHERE name LIKE 'J%'; -- starts with J
  • SELECT * FROM users WHERE name LIKE '%son%'; -- contains "son" anywhere
  • SELECT * FROM users WHERE name LIKE 'J___'; -- J + exactly 3 more chars
  • SELECT * FROM users WHERE name ILIKE 'john%'; -- PostgreSQL case-insensitive LIKE

4. Sorting & Limiting

4.1 ORDER BY

  • SELECT name, age FROM users
  • ORDER BY age; -- ascending (lowest first) by default
  • SELECT name, age FROM users
  • ORDER BY age DESC; -- descending (highest first)
  • SELECT name, city, age FROM users
  • ORDER BY city ASC, age DESC; -- city A→Z, then age high→low within each city

4.2 LIMIT and OFFSET

  • SELECT * FROM orders
  • ORDER BY created_at DESC
  • LIMIT 10; -- only return the 10 most recent orders
  • SELECT * FROM orders -- pagination: page 3, 10 rows per page
  • ORDER BY id
  • LIMIT 10 OFFSET 20; -- skip first 20, return next 10

4.3 NULL ordering

NULLs sort to the end by default in ascending order in most databases. To control this explicitly:

  • ORDER BY phone NULLS LAST; -- PostgreSQL
  • ORDER BY phone IS NULL, phone; -- MySQL trick

5. Aggregations & GROUP BY

Aggregate functions collapse many rows into one summary value.

5.1 Aggregate functions

FunctionWhat it doesExample
COUNT(*)Count all rowsSELECT COUNT(*) FROM users;
COUNT(col)Count non-NULL values in a columnSELECT COUNT(phone) FROM users;
COUNT(DISTINCT col)Count unique non-NULL valuesSELECT COUNT(DISTINCT city) FROM users;
SUM(col)Total of a numeric columnSELECT SUM(amount) FROM orders;
AVG(col)Average valueSELECT AVG(price) FROM products;
MIN(col)Smallest valueSELECT MIN(amount) FROM orders;
MAX(col)Largest valueSELECT MAX(amount) FROM orders;

5.2 GROUP BY — aggregate per group

Group rows that share a value, then aggregate each group.

  • SELECT city, COUNT(*) AS user_count
  • FROM users
  • GROUP BY city;
  • SELECT status, SUM(amount) AS total_revenue
  • FROM orders
  • GROUP BY status;
  • SELECT category, MIN(price) AS cheapest, MAX(price) AS most_expensive
  • FROM products
  • GROUP BY category
  • ORDER BY category;

Rule: every column in SELECT must either be in GROUP BY or inside an aggregate function.

  • -- ✅ correct
  • SELECT city, COUNT(*) FROM users GROUP BY city;
  • -- ❌ incorrect — name is not in GROUP BY and not aggregated
  • SELECT city, name, COUNT(*) FROM users GROUP BY city;

5.3 HAVING — filter groups

WHERE filters rows before grouping. HAVING filters groups after grouping.

  • SELECT city, COUNT(*) AS user_count
  • FROM users
  • GROUP BY city
  • HAVING COUNT(*) >= 5 -- only cities with 5 or more users
  • ORDER BY user_count DESC;
  • SELECT user_id, SUM(amount) AS total_spent
  • FROM orders
  • WHERE status = 'completed' -- filter rows first
  • GROUP BY user_id
  • HAVING SUM(amount) > 500 -- then filter groups
  • ORDER BY total_spent DESC;

5.4 Order of clauses (important!)

SQL clauses must appear in this order:

  1. SELECT
  2. FROM
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. ORDER BY
  8. LIMIT

6. JOINs

JOINs combine rows from two tables based on a related column.

6.1 INNER JOIN — only matching rows

Returns only rows where the join condition is true in both tables.

  • SELECT u.name, u.email, o.product, o.amount
  • FROM orders AS o
  • INNER JOIN users AS u ON o.user_id = u.id;

Any order without a matching user, or any user with no orders, is excluded.

6.2 LEFT JOIN — all rows from the left table

Returns all rows from the left table. Rows from the right table that don't match get NULL.

  • SELECT u.name, o.product, o.amount
  • FROM users AS u
  • LEFT JOIN orders AS o ON u.id = o.user_id;
  • -- users with no orders appear with NULL in product and amount

Common use: find users who have never placed an order

  • SELECT u.name
  • FROM users AS u
  • LEFT JOIN orders AS o ON u.id = o.user_id
  • WHERE o.id IS NULL;

6.3 RIGHT JOIN — all rows from the right table

Mirror of LEFT JOIN. Less common — most developers rewrite as LEFT JOIN with tables swapped.

  • SELECT u.name, o.product
  • FROM users AS u
  • RIGHT JOIN orders AS o ON u.id = o.user_id;
  • -- all orders appear; users without orders get NULL for name

6.4 FULL OUTER JOIN — all rows from both tables

  • SELECT u.name, o.product
  • FROM users AS u
  • FULL OUTER JOIN orders AS o ON u.id = o.user_id;
  • -- all users and all orders; NULLs where no match exists

6.5 Joining three tables

  • SELECT u.name, o.amount, p.name AS product_name, p.category
  • FROM orders AS o
  • JOIN users AS u ON o.user_id = u.id
  • JOIN products AS p ON o.product_id = p.id;

6.6 CROSS JOIN — every combination

  • SELECT u.name, p.name AS product
  • FROM users AS u
  • CROSS JOIN products AS p;
  • -- returns every user paired with every product (m × n rows)

6.7 Self JOIN — a table joined to itself

  • -- Example: employees table with manager_id referencing id
  • SELECT e.name AS employee, m.name AS manager
  • FROM employees AS e
  • LEFT JOIN employees AS m ON e.manager_id = m.id;

JOIN visual summary

JOIN typeRows returned
INNER JOINOnly rows matching in both tables
LEFT JOINAll rows from left + matched right (NULLs otherwise)
RIGHT JOINAll rows from right + matched left (NULLs otherwise)
FULL OUTER JOINAll rows from both tables
CROSS JOINEvery combination (Cartesian product)

7. Subqueries & CTEs

7.1 Subquery in WHERE

A subquery is a query nested inside another query.

  • -- Find users who have placed at least one order
  • SELECT name FROM users
  • WHERE id IN (SELECT DISTINCT user_id FROM orders);
  • -- Find products priced above average
  • SELECT name, price FROM products
  • WHERE price > (SELECT AVG(price) FROM products);

7.2 Subquery in FROM (derived table)

  • SELECT city, avg_age
  • FROM (
  •     SELECT city, AVG(age) AS avg_age
  •     FROM users
  •     GROUP BY city
  • ) AS city_stats
  • WHERE avg_age > 30;

7.3 Subquery in SELECT (scalar subquery)

  • SELECT name,
  •      (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
  • FROM users;

7.4 EXISTS and NOT EXISTS

  • -- Users who have placed at least one order
  • SELECT name FROM users u
  • WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  • -- Users who have never ordered
  • SELECT name FROM users u
  • WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

7.5 CTE — Common Table Expression (WITH clause)

A CTE is like a named subquery defined at the top of a query. It makes complex queries much more readable.

  • WITH high_spenders AS (
  •     SELECT user_id, SUM(amount) AS total_spent
  •     FROM orders
  •     WHERE status = 'completed'
  •     GROUP BY user_id
  •     HAVING SUM(amount) > 1000
  • )
  • SELECT u.name, u.email, h.total_spent
  • FROM high_spenders h
  • JOIN users u ON h.user_id = u.id
  • ORDER BY h.total_spent DESC;

Multiple CTEs

  • WITH
  • revenue AS (
  •     SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
  • ),
  • order_counts AS (
  •     SELECT user_id, COUNT(*) AS num_orders FROM orders GROUP BY user_id
  • )
  • SELECT u.name, r.total, oc.num_orders
  • FROM users u
  • JOIN revenue r ON u.id = r.user_id
  • JOIN order_counts oc ON u.id = oc.user_id;

8. Modifying Data

Warning: always run a matching SELECT with the same WHERE clause first to confirm which rows you will affect before running UPDATE or DELETE.

8.1 INSERT — add rows

  • -- Single row
  • INSERT INTO users (name, email, age, city)
  • VALUES ('Alice Smith', 'alice@email.com', 28, 'New York');
  • -- Multiple rows
  • INSERT INTO users (name, email, age, city)
  • VALUES
  •     ('Bob Jones', 'bob@email.com', 34, 'Chicago'),
  •     ('Carol Wu', 'carol@email.com', 22, 'Seattle');
  • -- Insert from a SELECT
  • INSERT INTO vip_users (user_id, total_spent)
  • SELECT user_id, SUM(amount)
  • FROM orders WHERE status = 'completed'
  • GROUP BY user_id HAVING SUM(amount) > 1000;

8.2 UPDATE — modify existing rows

  • -- Update a single row
  • UPDATE users
  • SET city = 'Los Angeles'
  • WHERE id = 42;
  • -- Update multiple columns
  • UPDATE users
  • SET city = 'Boston', age = 29
  • WHERE email = 'alice@email.com';
  • -- Update many rows at once
  • UPDATE orders
  • SET status = 'archived'
  • WHERE created_at < '2023-01-01';

8.3 DELETE — remove rows

  • -- Delete a single row
  • DELETE FROM users WHERE id = 42;
  • -- Delete by condition
  • DELETE FROM orders WHERE status = 'cancelled'
  •   AND created_at < '2022-01-01';
  • -- ⚠️ Delete ALL rows (dangerous!)
  • DELETE FROM temp_staging;

8.4 UPSERT (INSERT … ON CONFLICT)

Insert a row, or update it if the primary key already exists. Supported in PostgreSQL and SQLite.

  • INSERT INTO products (id, name, price)
  • VALUES (5, 'Widget', 9.99)
  • ON CONFLICT (id) DO UPDATE
  •   SET price = EXCLUDED.price;

8.5 TRUNCATE — delete all rows fast

  • TRUNCATE TABLE temp_staging; -- much faster than DELETE for full clears

9. Creating & Altering Tables

9.1 CREATE TABLE

  • CREATE TABLE users (
  •     id SERIAL PRIMARY KEY, -- auto-incrementing integer PK (PostgreSQL)
  •     name TEXT NOT NULL,
  •     email TEXT UNIQUE NOT NULL,
  •     age INTEGER,
  •     city TEXT,
  •     joined_at TIMESTAMP DEFAULT NOW()
  • );
  • CREATE TABLE orders (
  •     id SERIAL PRIMARY KEY,
  •     user_id INTEGER NOT NULL REFERENCES users(id), -- foreign key
  •     product TEXT NOT NULL,
  •     amount NUMERIC(10, 2) NOT NULL,
  •     status TEXT DEFAULT 'pending',
  •     created_at TIMESTAMP DEFAULT NOW()
  • );
  • CREATE TABLE products (
  •     id SERIAL PRIMARY KEY,
  •     name TEXT NOT NULL,
  •     category TEXT,
  •     price NUMERIC(10, 2) NOT NULL,
  •     stock INTEGER DEFAULT 0
  • );

9.2 Constraints

ConstraintEffect
PRIMARY KEYUnique + NOT NULL; identifies each row
NOT NULLColumn cannot be NULL
UNIQUEAll values in the column must be different
DEFAULT valueUsed when INSERT omits the column
REFERENCES table(col)Foreign key — value must exist in the referenced table
CHECK (expr)Row is only inserted if expression is true

9.3 ALTER TABLE

  • -- Add a column
  • ALTER TABLE users ADD COLUMN phone TEXT;
  • -- Drop a column
  • ALTER TABLE users DROP COLUMN phone;
  • -- Rename a column (PostgreSQL)
  • ALTER TABLE users RENAME COLUMN city TO location;
  • -- Change column type (PostgreSQL)
  • ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 2);
  • -- Add a NOT NULL constraint
  • ALTER TABLE users ALTER COLUMN name SET NOT NULL;

9.4 DROP TABLE

  • DROP TABLE temp_staging; -- ⚠️ permanently deletes the table and all data
  • DROP TABLE IF EXISTS temp_staging; -- no error if table doesn't exist

9.5 CREATE INDEX

Indexes speed up queries that filter or sort by specific columns. The database uses them automatically.

  • CREATE INDEX idx_orders_user_id ON orders(user_id);
  • CREATE INDEX idx_users_city ON users(city);
  • CREATE UNIQUE INDEX idx_users_email ON users(email);

Rule of thumb: index columns you frequently use in WHERE, JOIN ON, or ORDER BY. Don't over-index — each index slows down writes.

10. Window Functions

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does. They use the OVER() clause.

10.1 Syntax

  • function_name() OVER (
  •     PARTITION BY partition_column -- optional: reset the window per group
  •     ORDER BY sort_column -- optional: order within window
  • )

10.2 ROW_NUMBER — rank each row

  • SELECT name, city, age,
  •      ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS rank_in_city
  • FROM users;
  • -- rank 1 = oldest user in each city

Top-N per group (using ROW_NUMBER in a CTE)

  • WITH ranked AS (
  •     SELECT user_id, amount,
  •         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
  •     FROM orders
  • )
  • SELECT * FROM ranked WHERE rn = 1; -- largest order per user

10.3 RANK and DENSE_RANK

  • SELECT name, amount,
  •      RANK() OVER (ORDER BY amount DESC) AS rank,
  •      DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
  • FROM orders;
FunctionTies behaviorExample (amounts: 100, 100, 80)
ROW_NUMBERAlways unique — ties broken arbitrarily1, 2, 3
RANKTies get same rank; next rank skips1, 1, 3
DENSE_RANKTies get same rank; next rank does not skip1, 1, 2

10.4 Running totals and moving averages

  • SELECT created_at, amount,
  •      SUM(amount) OVER (ORDER BY created_at) AS running_total
  • FROM orders
  • ORDER BY created_at;
  • -- 7-day moving average
  • SELECT created_at, amount,
  •      AVG(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
  • FROM orders;

10.5 LAG and LEAD — access adjacent rows

  • SELECT created_at, amount,
  •      LAG(amount) OVER (ORDER BY created_at) AS prev_amount,
  •      LEAD(amount) OVER (ORDER BY created_at) AS next_amount,
  •      amount - LAG(amount) OVER (ORDER BY created_at) AS change_from_prev
  • FROM orders;

10.6 NTILE — split into buckets

  • SELECT name, amount,
  •      NTILE(4) OVER (ORDER BY amount DESC) AS quartile
  • FROM orders;
  • -- quartile 1 = top 25%, quartile 4 = bottom 25%

10.7 FIRST_VALUE / LAST_VALUE

  • SELECT user_id, amount,
  •      FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order_amt,
  •      LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at
  •         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_amt
  • FROM orders;

10.8 Window function quick reference

FunctionUse case
ROW_NUMBER()Unique sequential number per row
RANK()Rank with gaps on ties
DENSE_RANK()Rank without gaps on ties
NTILE(n)Divide rows into n equal buckets
SUM() OVERRunning or partitioned total
AVG() OVERRunning or moving average
COUNT() OVERRunning or partitioned count
LAG(col, n)Value from n rows before current row
LEAD(col, n)Value from n rows after current row
FIRST_VALUE(col)First value in the window
LAST_VALUE(col)Last value in the window

Quick Reference Card

Full SELECT anatomy

  • SELECT col1, col2, AGG(col3) AS alias
  • FROM table_a AS a
  • JOIN table_b AS b ON a.id = b.a_id
  • WHERE a.status = 'active' AND b.amount > 50
  • GROUP BY col1, col2
  • HAVING AGG(col3) > 100
  • ORDER BY alias DESC
  • LIMIT 25 OFFSET 0;

Common interview patterns

  • -- Nth highest value
  • SELECT amount FROM orders ORDER BY amount DESC LIMIT 1 OFFSET (n-1);
  • -- Duplicate rows
  • SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
  • -- Percentage of total
  • SELECT category, SUM(amount) AS cat_total,
  •      ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct
  • FROM orders GROUP BY category;
  • -- First event per user
  • SELECT DISTINCT ON (user_id) user_id, created_at, product
  • FROM orders ORDER BY user_id, created_at; -- PostgreSQL
  • -- Cohort by signup month
  • SELECT DATE_TRUNC('month', joined_at) AS cohort, COUNT(*) AS signups
  • FROM users GROUP BY 1 ORDER BY 1;

11. Google SQL Interview — Product Strategy & Operations (StratOps)

Congratulations on landing an interview for the Product Strategy & Operations (StratOps) role! This is a highly sought-after position that sits at the intersection of product management and business analytics.

For StratOps, the SQL test is designed to see if you can translate business questions into data queries. Google doesn't expect you to be a database engineer, but they do need you to be "data-fluent."

Test Format

While individual teams vary, the SQL assessment typically follows one of two formats:

  • Live Coding (Most Common): A 30–45 minute video call where you share your screen (using a tool like Google Docs or a simple code editor). You will be given a set of tables and asked to write queries in real-time while explaining your logic.
  • Take-Home / Online Assessment: A timed platform test (like HackerRank or CoderPad) featuring 3–5 problems ranging from basic filtering to complex data manipulation.

What's on the Test?

The questions will almost always be framed around Google products (e.g., YouTube watch time, Google Ads performance, or Play Store transactions).

1. Core SQL Fundamentals

You must be comfortable with the "bread and butter" of SQL. Expect to use:

  • Aggregations: SUM, AVG, COUNT(DISTINCT ...), and the essential GROUP BY.
  • Joins: Knowing when to use LEFT JOIN vs. INNER JOIN is critical. You might be asked to join a "User" table with an "Activity" table to find inactive users.
  • Filtering: WHERE vs. HAVING (this is a classic trap).

2. Intermediate Concepts

This is where StratOps candidates are usually differentiated:

  • Window Functions: Be ready to use RANK(), DENSE_RANK(), or ROW_NUMBER(). For example: "Find the top 3 most-viewed YouTube categories per country."
  • Date/Time Manipulation: Calculating Month-over-Month (MoM) growth or identifying users who signed up in the last 30 days.
  • CTEs: Use WITH clauses to keep your code organized. Google interviewers love clean, readable code.

3. Business Logic & Case Integration

Expect "Case-style" SQL questions. Instead of being told "Join Table A and B," you'll be told:

"Our Ads revenue dropped by 5% in EMEA last month. Write a query to identify which ad formats (Search, Display, Video) contributed most to this decline."

Preparation Checklist

TopicFocus Area
JoinsUnderstand Self-Joins (e.g., comparing a user's behavior today vs. yesterday)
Case LogicUse CASE WHEN to bucket data (e.g., "Small," "Medium," "Large" advertisers)
WindowingPractice calculating running totals or period-over-period changes
SyntaxGoogle typically uses Standard SQL (BigQuery syntax), which is very similar to Postgres
Pro-Tip: Think Aloud. At Google, your thought process is often more important than perfect syntax. If you get stuck, explain what you are trying to achieve (e.g., "I'm trying to filter for the latest timestamp per user before I join it to the revenue table"). The interviewer will often give you a nudge in the right direction.

50 Practice Questions & Answers

These are categorized by difficulty and focus on business-centric problems you'll likely encounter. Questions assume BigQuery / Standard SQL syntax.

I. Basic: Filtering, Aggregation, and Joins (Q1–15)

  1. Find the total number of unique users who were active on YouTube yesterday.
    • SELECT COUNT(DISTINCT user_id)
    • FROM youtube_activity
    • WHERE date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
  2. List all Google Cloud customers who spent more than $10,000 last month.
    • SELECT customer_name
    • FROM cloud_billing
    • WHERE billing_month = '2023-10'
    • GROUP BY customer_name
    • HAVING SUM(spend) > 10000;
  3. Calculate the average rating for every app category in the Play Store.
    • SELECT category, AVG(rating) AS avg_rating
    • FROM play_store_apps
    • GROUP BY category;
  4. Find users who have never made a purchase (Users + Orders tables).
    • SELECT u.user_id
    • FROM Users u
    • LEFT JOIN Orders o ON u.user_id = o.user_id
    • WHERE o.order_id IS NULL;
  5. Count how many videos were uploaded to YouTube per country today.
    • SELECT country, COUNT(video_id) AS uploads
    • FROM uploads
    • WHERE upload_date = CURRENT_DATE()
    • GROUP BY country;
  6. Find the top 5 most expensive products in the Google Store.
    • SELECT product_name, price
    • FROM google_store_products
    • ORDER BY price DESC
    • LIMIT 5;
  7. Show all Gmail users whose accounts were created in 2022.
    • SELECT email
    • FROM gmail_users
    • WHERE EXTRACT(YEAR FROM created_at) = 2022;
  8. Get total revenue generated by "Search" ads vs. "Display" ads.
    • SELECT ad_type, SUM(revenue) AS total_revenue
    • FROM ads_performance
    • GROUP BY ad_type;
  9. Find the number of Android users on a version higher than 12.
    • SELECT COUNT(user_id)
    • FROM devices
    • WHERE android_version > 12;
  10. Select all active Google Workspace subscriptions set to "Annual" billing.
    • SELECT sub_id
    • FROM subscriptions
    • WHERE status = 'Active' AND billing_cycle = 'Annual';
  11. What is the maximum storage used by a single Google Drive user?
    • SELECT MAX(storage_gb) AS max_storage
    • FROM drive_usage;
  12. List all YouTube creators with more than 1 million subscribers.
    • SELECT channel_name
    • FROM creators
    • WHERE subscriber_count > 1000000;
  13. Find the total number of support tickets closed by each agent today.
    • SELECT agent_id, COUNT(ticket_id) AS closed_today
    • FROM tickets
    • WHERE status = 'Closed' AND closed_date = CURRENT_DATE()
    • GROUP BY agent_id;
  14. Combine First_Name and Last_Name into a Full_Name column.
    • SELECT CONCAT(first_name, ' ', last_name) AS full_name
    • FROM employees;
  15. Find all search queries that contain the word "Pixel".
    • SELECT query
    • FROM search_logs
    • WHERE query LIKE '%Pixel%';

II. Intermediate: CTEs, Case Logic, and Dates (Q16–35)

  1. Bucket advertisers into 'Small' (<$1k), 'Medium' ($1k–$10k), and 'Large' (>$10k).
    • SELECT advertiser_id,
    •   CASE
    •     WHEN SUM(spend) < 1000 THEN 'Small'
    •     WHEN SUM(spend) BETWEEN 1000 AND 10000 THEN 'Medium'
    •     ELSE 'Large'
    •   END AS tier
    • FROM ads
    • GROUP BY advertiser_id;
  2. Calculate Month-over-Month growth in YouTube Premium subscribers.
    • WITH monthly AS (
    •   SELECT DATE_TRUNC(signup_date, MONTH) AS month,
    •        COUNT(*) AS subs
    •   FROM premium_signups
    •   GROUP BY 1
    • )
    • SELECT month, subs,
    •   LAG(subs) OVER (ORDER BY month) AS prev_subs,
    •   ROUND(100.0 * (subs - LAG(subs) OVER (ORDER BY month))
    •     / LAG(subs) OVER (ORDER BY month), 2) AS mom_pct
    • FROM monthly;
  3. Find users who logged in on both Monday and Tuesday of this week.
    • SELECT user_id FROM logins WHERE date = '2023-11-06'
    • INTERSECT DISTINCT
    • SELECT user_id FROM logins WHERE date = '2023-11-07';
  4. Identify "Power Users" who used Google Search at least 5 days in the last week.
    • SELECT user_id
    • FROM search_logs
    • WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    • GROUP BY user_id
    • HAVING COUNT(DISTINCT date) >= 5;
  5. For each country, find the app with the highest number of downloads.
    • WITH ranked AS (
    •   SELECT country, app_name, downloads,
    •        RANK() OVER (PARTITION BY country ORDER BY downloads DESC) AS rk
    •   FROM app_downloads
    • )
    • SELECT country, app_name, downloads
    • FROM ranked WHERE rk = 1;
  6. Calculate the 7-day rolling average of Google Maps searches.
    • SELECT date,
    •   AVG(search_count) OVER (
    •     ORDER BY date
    •     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    •   ) AS rolling_7d_avg
    • FROM maps_stats;
  7. Find the time difference (in hours) between a user signing up and their first search.
    • SELECT user_id,
    •   TIMESTAMP_DIFF(first_search_at, signup_at, HOUR) AS hours_to_first_search
    • FROM user_journey;
  8. List the second most popular video in each category.
    • WITH ranked AS (
    •   SELECT category, video_id, views,
    •        DENSE_RANK() OVER (PARTITION BY category ORDER BY views DESC) AS rk
    •   FROM videos
    • )
    • SELECT * FROM ranked WHERE rk = 2;
  9. Convert a Unix timestamp to a readable date (BigQuery).
    • SELECT TIMESTAMP_SECONDS(unix_column) AS readable_ts
    • FROM your_table;
  10. Find users who upgraded from Free to Paid within 24 hours.
    • SELECT t1.user_id
    • FROM plan_changes t1
    • JOIN plan_changes t2
    •   ON t1.user_id = t2.user_id
    •   AND t1.plan = 'Free'
    •   AND t2.plan = 'Paid'
    •   AND TIMESTAMP_DIFF(t2.changed_at, t1.changed_at, HOUR) <= 24;
  11. Retrieve the last search query performed by each user today.
    • WITH ranked AS (
    •   SELECT user_id, query,
    •        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) AS rn
    •   FROM search_logs
    •   WHERE DATE(timestamp) = CURRENT_DATE()
    • )
    • SELECT user_id, query FROM ranked WHERE rn = 1;
  12. Calculate the "Take Rate" (Revenue / GMV) for the Play Store.
    • SELECT ROUND(SUM(revenue) / SUM(gmv), 4) AS take_rate
    • FROM transactions;
  13. Which YouTube channels have zero uploads in the last 90 days?
    • SELECT channel_id
    • FROM channels
    • WHERE channel_id NOT IN (
    •   SELECT channel_id FROM uploads
    •   WHERE date > DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    • );
  14. Find the median spend of all Google Cloud customers.
    • SELECT PERCENTILE_CONT(spend, 0.5) OVER() AS median_spend
    • FROM cloud_billing
    • LIMIT 1;
  15. List all pairs of users who share the same IP address.
    • SELECT a.user_id AS user_a, b.user_id AS user_b
    • FROM logins a
    • JOIN logins b ON a.ip = b.ip
    • WHERE a.user_id < b.user_id; -- avoids duplicate pairs
  16. Calculate the click-through rate (CTR) of Search results.
    • SELECT
    •   ROUND(100.0 * SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS ctr_pct
    • FROM search_results;
  17. Find total revenue per quarter for the last two years.
    • SELECT EXTRACT(YEAR FROM date) AS yr,
    •        EXTRACT(QUARTER FROM date) AS qtr,
    •        SUM(revenue) AS total
    • FROM sales
    • WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR)
    • GROUP BY 1, 2
    • ORDER BY 1, 2;
  18. Replace all NULL values in a 'Comments' column with 'No Comment'.
    • SELECT COALESCE(comments, 'No Comment') AS comments
    • FROM feedback;
  19. Find the top 10% of users by data usage on Google Photos.
    • WITH buckets AS (
    •   SELECT user_id, data_gb,
    •        NTILE(10) OVER (ORDER BY data_gb DESC) AS decile
    •   FROM photos_usage
    • )
    • SELECT user_id, data_gb FROM buckets WHERE decile = 1;
  20. Determine if a string is a valid email (basic @ check, BigQuery).
    • SELECT email
    • FROM users
    • WHERE REGEXP_CONTAINS(email, r'@');

III. Advanced: Business Logic & Complex Analytics (Q36–50)

  1. Retention: What % of Day-0 users returned on Day 7?
    • WITH d0 AS (
    •   SELECT DISTINCT user_id FROM activity WHERE date = '2023-11-01'
    • ),
    • d7 AS (
    •   SELECT DISTINCT user_id FROM activity WHERE date = '2023-11-08'
    • )
    • SELECT ROUND(100.0 * COUNT(d7.user_id) / COUNT(d0.user_id), 2) AS day7_retention_pct
    • FROM d0
    • LEFT JOIN d7 USING (user_id);
  2. Cannibalization: Did YouTube Shorts reduce long-form watch time?
    • WITH first_shorts AS (
    •   SELECT user_id, MIN(date) AS first_shorts_date FROM shorts_views GROUP BY user_id
    • )
    • SELECT
    •   AVG(CASE WHEN lv.date < fs.first_shorts_date THEN lv.watch_minutes END) AS before_shorts,
    •   AVG(CASE WHEN lv.date >= fs.first_shorts_date THEN lv.watch_minutes END) AS after_shorts
    • FROM longform_views lv
    • JOIN first_shorts fs ON lv.user_id = fs.user_id
    • WHERE lv.date BETWEEN DATE_SUB(fs.first_shorts_date, INTERVAL 30 DAY)
    •           AND DATE_ADD(fs.first_shorts_date, INTERVAL 30 DAY);
  3. Anomaly Detection: Find any day where Ads revenue dropped >20% vs. prior day.
    • WITH daily AS (
    •   SELECT date, SUM(revenue) AS rev,
    •        LAG(SUM(revenue)) OVER (ORDER BY date) AS prev_rev
    •   FROM ads_revenue GROUP BY date
    • )
    • SELECT date, rev, prev_rev,
    •   ROUND((rev - prev_rev) / prev_rev * 100, 2) AS pct_change
    • FROM daily
    • WHERE (rev - prev_rev) / prev_rev < -0.20;
  4. Attribution: Find the first marketing channel each user touched before purchase.
    • SELECT DISTINCT user_id,
    •   FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touchpoint_time) AS first_channel
    • FROM touchpoints
    • WHERE touchpoint_time < purchase_time;
  5. Sessionization: Assign session IDs where actions >30 min apart start a new session.
    • WITH gaps AS (
    •   SELECT user_id, timestamp,
    •        CASE WHEN TIMESTAMP_DIFF(timestamp,
    •          LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), MINUTE) > 30
    •          OR LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL
    •        THEN 1 ELSE 0 END AS new_session
    •   FROM events
    • )
    • SELECT user_id, timestamp,
    •   SUM(new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_id
    • FROM gaps;
  6. Market Share: Percentage of total search volume by browser.
    • SELECT browser,
    •   ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS share_pct
    • FROM search_logs
    • GROUP BY browser
    • ORDER BY share_pct DESC;
  7. Churn: Users active every month for 6 months but not in the last 30 days.
    • WITH regulars AS (
    •   SELECT user_id
    •   FROM activity
    •   WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
    •                 AND DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    •   GROUP BY user_id
    •   HAVING COUNT(DISTINCT DATE_TRUNC(date, MONTH)) = 6
    • )
    • SELECT r.user_id
    • FROM regulars r
    • WHERE r.user_id NOT IN (
    •   SELECT user_id FROM activity
    •   WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    • );
  8. Upsell: Identify users who reached 90% of their Google One storage limit.
    • SELECT user_id,
    •   ROUND(100.0 * used_bytes / limit_bytes, 1) AS pct_used
    • FROM storage
    • WHERE (used_bytes / limit_bytes) >= 0.9;
  9. ABC Analysis: Classify products as A (top 80% revenue), B (next 15%), C (bottom 5%).
    • WITH totals AS (
    •   SELECT product_id, SUM(revenue) AS rev FROM sales GROUP BY 1
    • ),
    • running AS (
    •   SELECT product_id, rev,
    •        SUM(rev) OVER (ORDER BY rev DESC) AS running_total,
    •        SUM(rev) OVER () AS grand_total
    •   FROM totals
    • )
    • SELECT product_id,
    •   CASE
    •     WHEN running_total / grand_total <= 0.80 THEN 'A'
    •     WHEN running_total / grand_total <= 0.95 THEN 'B'
    •     ELSE 'C'
    •   END AS abc_class
    • FROM running;
  10. Cross-Sell: Most common app bought by users who also bought "Minecraft".
    • SELECT p.product_name, COUNT(*) AS co_purchases
    • FROM purchases mc
    • JOIN purchases p ON mc.user_id = p.user_id
    •   AND p.product_name != 'Minecraft'
    • WHERE mc.product_name = 'Minecraft'
    • GROUP BY p.product_name
    • ORDER BY co_purchases DESC
    • LIMIT 10;
  11. Funnel Analysis: Drop-off rate between "Add to Cart" and "Checkout Complete".
    • WITH steps AS (
    •   SELECT
    •     COUNT(DISTINCT CASE WHEN event = 'add_to_cart' THEN user_id END) AS add_to_cart,
    •     COUNT(DISTINCT CASE WHEN event = 'checkout_complete' THEN user_id END) AS completed
    •   FROM funnel_events
    • )
    • SELECT add_to_cart, completed,
    •   ROUND(100.0 * (add_to_cart - completed) / add_to_cart, 2) AS dropoff_pct
    • FROM steps;
  12. Seasonality: Which day of the week has the highest Google Maps traffic for "Restaurants"?
    • SELECT FORMAT_DATE('%A', date) AS day_of_week,
    •   SUM(searches) AS total_searches
    • FROM maps_traffic
    • WHERE category = 'Restaurants'
    • GROUP BY day_of_week
    • ORDER BY total_searches DESC
    • LIMIT 1;
  13. LTV: Calculate total revenue generated by a user since their join date.
    • SELECT u.user_id, u.joined_at,
    •   COALESCE(SUM(o.amount), 0) AS lifetime_value
    • FROM users u
    • LEFT JOIN orders o ON u.user_id = o.user_id
    •   AND o.created_at >= u.joined_at
    • GROUP BY u.user_id, u.joined_at
    • ORDER BY lifetime_value DESC;
  14. Data Integrity: Find transactions where tax_amount is not exactly 8% of subtotal.
    • SELECT transaction_id, subtotal, tax_amount
    • FROM transactions
    • WHERE ABS(tax_amount - ROUND(subtotal * 0.08, 2)) > 0.01;
  15. Efficiency: Average number of searches before a user clicks a "Help" article.
    • WITH pre_help AS (
    •   SELECT s.user_id, s.session_id, COUNT(*) AS searches_before_help
    •   FROM searches s
    •   JOIN help_clicks h
    •     ON s.user_id = h.user_id
    •     AND s.timestamp < h.clicked_at
    •   GROUP BY s.user_id, s.session_id
    • )
    • SELECT AVG(searches_before_help) AS avg_searches
    • FROM pre_help;
Practice Tip: Don't just read these — go to BigQuery Public Datasets or DB Fiddle, create small tables, and actually write the code. Focus on the Intermediate section (Q16–35), as that's where most Google StratOps interviews live.