Learn SQL by Playing

Show all students who scored grade 'A'. Use the WHERE clause to filter rows — only rows where grade = 'A' should appear in the result.

SELECT *
FROM students
WHERE grade = 'A';

Show all products sorted by price from lowest to highest. Then try changing it to highest-to-lowest by adding DESC. ORDER BY always comes at the end of a query.

SELECT id, name, price
FROM products
ORDER BY price ASC;  -- ASC is default; swap DESC for highest first

Find all customers whose name starts with the letter 'A'. Use LIKE 'A%' — the % wildcard matches any characters after 'A'. Try LIKE '%a' in the playground to find names ending with 'a'.

SELECT *
FROM customers
WHERE name LIKE 'A%';

-- Try also:
-- WHERE name LIKE '%a'   -- ends with 'a'
-- WHERE name LIKE '_i%'  -- second letter is 'i'

Count how many students are in each city. Use GROUP BY city to group rows by city, then COUNT(*) to count how many students fall in each group.

SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city
ORDER BY city;

Show each order with the customer's name and amount. The customers table has names; orders has amounts. Use INNER JOIN to match them on customer_id.

SELECT o.order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_id;

The HR team needs a report with only employee name and department — they don't want salary or phone visible. Select just those two columns from the employees table.

SELECT name, department
FROM employees;

Show all products where price is greater than ₹100. Then try modifying the query to show products priced less than ₹50 and products where category is NOT 'Food'.

SELECT *
FROM products
WHERE price > 100;

-- Also try:
-- WHERE price < 50          -- cheaper than ₹50
-- WHERE category != 'Food'  -- everything except Food

Part A — Show employees in the 'Sales' department AND with salary above ₹40,000.
Part B — Show customers from 'Delhi' OR 'Pune'.

-- Part A: AND — both conditions must be true
SELECT *
FROM employees
WHERE department = 'Sales' AND salary > 40000;

-- Part B: OR — either condition is enough
-- SELECT * FROM customers WHERE city = 'Delhi' OR city = 'Pune';

The orders table has hundreds of rows but you only need to know which unique cities orders have come from. Use SELECT DISTINCT to get each city name exactly once.

SELECT DISTINCT city
FROM orders
ORDER BY city;

Find all employees who have not provided a phone number (phone is NULL). Then find those who have provided one (IS NOT NULL).

SELECT *
FROM employees
WHERE phone IS NULL;

-- Find employees who DO have a phone:
-- WHERE phone IS NOT NULL

Show all students sorted by score descending (highest first). When two students have the same score, sort their names alphabetically (A–Z).

SELECT name, score
FROM students
ORDER BY score DESC, name ASC;

Show all products that belong to the categories 'Electronics', 'Books', or 'Clothing'. Use IN instead of three separate OR conditions.

SELECT name, category, price
FROM products
WHERE category IN ('Electronics', 'Books', 'Clothing')
ORDER BY category;

Show all orders where the amount is between ₹500 and ₹1000 (both ends included). Works for numbers, dates, and text ranges.

SELECT customer, amount
FROM orders
WHERE amount BETWEEN 500 AND 1000
ORDER BY amount;

Find the highest and lowest salary in the company with a single query. Also find the most recent joining date using MAX on a date column.

SELECT
  MAX(salary)    AS highest_salary,
  MIN(salary)    AS lowest_salary,
  MAX(join_date) AS latest_join
FROM employees;

Calculate the total revenue and average order value from the sales table in a single query. Round the average to 2 decimal places.

SELECT
  SUM(amount)            AS total_revenue,
  ROUND(AVG(amount), 2) AS avg_order
FROM sales;

Show only customers whose total order value exceeds ₹1,000. You need GROUP BY to group per customer, then HAVING to filter on the total.

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 1000
ORDER BY total DESC;

Display each employee's full name (first + last concatenated) as full_name, and rename salary to monthly_pay. Use AS to create column aliases.

SELECT
  first_name || ' ' || last_name AS full_name,
  salary AS monthly_pay
FROM employees;

Find all customers who have never placed an order. INNER JOIN would drop them — LEFT JOIN keeps them with NULLs, then filter for those NULLs.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.name;

Build a combined list of cities from both the customers and suppliers tables — no duplicates. Then try UNION ALL to see duplicates included.

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

-- Try UNION ALL to include the duplicate 'Mumbai':
-- SELECT city FROM customers UNION ALL SELECT city FROM suppliers

Query all columns for every city in Maharashtra (state_code = 'MH') with a population greater than 500,000.

SELECT *
FROM city
WHERE state_code = 'MH'
  AND population > 500000;

List the names of all cities in Rajasthan (state_code = 'RJ'), sorted alphabetically.

SELECT name
FROM city
WHERE state_code = 'RJ'
ORDER BY name ASC;

List distinct city names where the city's ID is an even number. Use the modulo operator % (or MOD(id, 2) = 0) to check for even numbers.

SELECT DISTINCT name
FROM city
WHERE id % 2 = 0
ORDER BY name;

Find how many duplicate city names exist in the table. Subtract the number of distinct names from the total count — the difference equals the duplicate count.

SELECT
  COUNT(name) - COUNT(DISTINCT name) AS duplicate_count
FROM city;

A country is classified as "big" if it has an area of at least 3,000,000 km² OR a population of at least 25,000,000. Report the name, population, and area for all big countries.

SELECT name, population, area
FROM world
WHERE area >= 3000000
   OR population >= 25000000;

Find the names of customers who were not referred by customer with id = 2. Include customers whose referee_id is NULL (they were referred by no one).

SELECT name
FROM customer
WHERE referee_id != 2
   OR referee_id IS NULL
ORDER BY name;

Find all customers who have never placed an order. Use LEFT JOIN to keep all customers, then filter for rows where the order side is NULL.

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL
ORDER BY c.name;

Find all email addresses that appear more than once in the Person table. Use GROUP BY to group by email, then HAVING to filter groups with more than 1 row.

SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

Find all classes that have at least 5 students enrolled. Each row in the table represents one student enrolled in one class.

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5;

Show all movies with an odd ID and a description that is not 'boring', ordered by rating descending.

SELECT *
FROM Cinema
WHERE id % 2 = 1
  AND description != 'boring'
ORDER BY rating DESC;

Find all authors who viewed at least one of their own articles (i.e. author_id = viewer_id). Return distinct author IDs sorted ascending.

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

You want to show only departments where the total salary is above ₹50,000. Your query already has GROUP BY department. Which clause do you add?

A phone column has some rows with no value stored. Which query correctly finds those rows?

The orders table has 1,000 rows but only 40 unique cities. What does SELECT DISTINCT city FROM orders return?

You write a query with SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. In which order does SQL actually execute these clauses?
Hint: this is why you can't use a SELECT alias inside WHERE.

You LEFT JOIN customers (5 rows) with orders (3 rows). Two customers have no orders at all. How many rows appear in the result?

A status column has 10 rows, but 3 of them are NULL. What does COUNT(status) return?

Which pattern matches any name that starts with 'A' and ends with 'n', with any number of characters in between (like 'Arjun', 'Adrian', or even just 'An')?

You write SELECT name FROM employees ORDER BY salary without specifying ASC or DESC. In what order are results returned?

Table A has 3 rows, Table B has 3 rows. One row exists in both tables. UNION gives ___ rows; UNION ALL gives ___ rows.

Table students has id as PRIMARY KEY. Row with id=1 already exists. You try:
INSERT INTO students VALUES(1, 'Riya') — what happens? And which general rule does that reveal about PRIMARY KEY?

You have 5 customers and 3 orders. Two customers have never ordered. You INNER JOIN customers to orders. How many rows appear in the result?

A student table has ages 17, 18, 20, 25, 26. Query: WHERE age BETWEEN 18 AND 25. Which ages appear in the result?

Table employees before grouping:

After GROUP BY department, HR collapses to 1 row. SQL can't decide which name (Arjun or Priya?) to show. Which SELECT line causes an error?

After running DELETE FROM employees, what is left in the database?

A row has phone = NULL and email = 'p@co.in'. What does COALESCE(phone, email, 'No contact') return?

A student scores 85. What does this return?
CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END

orders.customer_id has a FOREIGN KEY referencing customers.id. What does this prevent?

You add CREATE INDEX idx_email ON users(email). What is the primary benefit?

Table employees — notice manager_id points to another row in the same table:

Priya (₹95k) earns more than her manager Sunita (₹90k). To find such employees, you use:

You want the 3 most expensive products from the products table. Which query is correct in SQLite?

Query: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees). What does the inner SELECT AVG(salary) return?

WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id) — what does this check for each customer row?

Write a SQL query to find all duplicate email addresses in the Person table. An email is a duplicate if it appears more than once.

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

Find all customers who have never placed an order. A classic LEFT JOIN + NULL check pattern asked in almost every SQL interview.

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;

Write a query to find employees whose salary is higher than their direct manager's salary. Uses a self-join on the Employee table using managerId.

SELECT e.name AS Employee
FROM Employee e
JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;

Calculate the average star rating per product category, rounded to 2 decimal places, ordered by category name. A common warm-up question in Amazon data analyst rounds.

SELECT p.category,
  ROUND(AVG(r.stars), 2) AS avg_rating
FROM reviews r
JOIN products p ON r.product_id = p.product_id
GROUP BY p.category
ORDER BY p.category;

Find all dates where the temperature was higher than the previous day. Use a self-join on the Weather table and julianday() arithmetic to link each row to the row exactly 1 day earlier.

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON julianday(w1.recordDate) - julianday(w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

Write a query to find the second highest distinct salary. If there is no second highest salary, return NULL. Most asked SQL question globally — know 3 approaches.

-- Approach 1: Subquery (most readable)
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

-- Approach 2: LIMIT + OFFSET
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Approach 3: Window function (preferred in interviews)
SELECT salary AS SecondHighestSalary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
) t
WHERE rnk = 2;

Find the top 5 customers by total number of orders placed. Show their name and order count, highest first. Frequently asked in Flipkart/e-commerce data rounds.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY order_count DESC
LIMIT 5;

Find the top 3 unique salaries in each department. This is the most commonly asked window function question. Uses DENSE_RANK so ties are handled correctly.

WITH ranked AS (
  SELECT d.name AS Department,
         e.name AS Employee,
         e.salary,
         DENSE_RANK() OVER (
           PARTITION BY e.deptId
           ORDER BY e.salary DESC
         ) AS rnk
  FROM Employee e
  JOIN Department d ON e.deptId = d.id
)
SELECT Department, Employee, salary
FROM ranked
WHERE rnk <= 3;

Find employees whose salary is above the average salary of their own department. Classic correlated subquery or CTE — commonly asked at Microsoft, LinkedIn, and Oracle.

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN dept_avg d ON e.dept = d.dept
WHERE e.salary > d.avg_sal
ORDER BY e.dept, e.salary DESC;

Calculate the month-over-month revenue growth percentage. Use LAG() to access the previous month's revenue and compute the percentage change.

WITH monthly AS (
  SELECT
    strftime('%Y-%m', order_date) AS month,
    SUM(amount) AS revenue
  FROM monthly_orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    * 100.0
    / LAG(revenue) OVER (ORDER BY month), 2
  ) AS growth_pct
FROM monthly;

Calculate a 7-day rolling average of daily active users. Asked extensively at Google, Twitter, and analytics-heavy companies. Tests window frame knowledge.

SELECT
  activity_date,
  active_users,
  ROUND(
    AVG(active_users) OVER (
      ORDER BY activity_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) AS rolling_7d_avg
FROM user_activity
ORDER BY activity_date;

Find all restaurants where the order count this month is lower than the previous month. Directly from Swiggy data analyst interview rounds.

WITH monthly AS (
  SELECT
    restaurant_id, month, order_count,
    LAG(order_count) OVER (
      PARTITION BY restaurant_id
      ORDER BY month
    ) AS prev_count
  FROM restaurant_orders
)
SELECT restaurant_id, month, order_count, prev_count
FROM monthly
WHERE order_count < prev_count;

Find customers who placed at least one order in both 2023 and 2024 — a classic cohort retention question asked in Flipkart and e-commerce data interviews.

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2023

INTERSECT

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2024;

Rank all scores from highest to lowest. If two scores are tied, they should have the same rank. The next rank after a tie should NOT skip numbers — this is what DENSE_RANK does.

SELECT
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores
ORDER BY score DESC;

Calculate a cumulative/running total of daily sales ordered by date. The most commonly tested window frame concept at Microsoft and Oracle interviews.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Find the median salary of employees in each department. There's no built-in MEDIAN in MySQL — requires row numbering + even/odd count logic. Frequently asked at Amazon and top analytics roles.

WITH ranked AS (
  SELECT id, company, salary,
    ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary) AS rn,
    COUNT(*) OVER (PARTITION BY company) AS cnt
  FROM Employee
)
SELECT id, company, salary
FROM ranked
WHERE rn IN (
  FLOOR((cnt + 1) / 2),
  CEIL((cnt + 1) / 2)
);

Find all rows where 3 or more consecutive days had stadium traffic ≥ 100 people. The key insight: id - ROW_NUMBER() is constant for consecutive rows — the famous "gap-and-island" trick.

WITH high_traffic AS (
  SELECT id, visit_date, people,
    id - ROW_NUMBER() OVER (ORDER BY id) AS grp
  FROM Stadium
  WHERE people >= 100
),
groups AS (
  SELECT grp
  FROM high_traffic
  GROUP BY grp
  HAVING COUNT(*) >= 3
)
SELECT h.id, h.visit_date, h.people
FROM high_traffic h
JOIN groups g ON h.grp = g.grp
ORDER BY h.visit_date;

Calculate the cancellation rate of requests made by unbanned users between two dates. This multi-join + conditional aggregation problem appears in Uber, Lyft, and analytics interviews.

SELECT
  t.request_at AS Day,
  ROUND(
    SUM(CASE WHEN t.status != 'completed' THEN 1 ELSE 0 END)
    * 1.0 / COUNT(*), 2
  ) AS 'Cancellation Rate'
FROM Trips t
JOIN Users u ON t.client_id = u.users_id
  AND u.banned = 'No'
WHERE t.request_at BETWEEN '2024-10-01' AND '2024-10-03'
GROUP BY t.request_at;

Find the names of managers who have at least 5 direct reports. A self-join aggregation problem asked heavily at Amazon, Google, and LinkedIn.

SELECT m.name
FROM Employee e
JOIN Employee m ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(e.id) >= 5;

Find each user's longest consecutive daily login streak. This uses the classic gap-and-island technique: subtracting ROW_NUMBER from the date to group consecutive days. Asked at Meta and Google.

WITH gaps AS (
  SELECT user_id, login_date,
    date(login_date, '-' || ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) || ' days') AS grp
  FROM logins
),
streaks AS (
  SELECT user_id, grp, COUNT(*) AS streak_len
  FROM gaps
  GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM streaks
GROUP BY user_id;

Zomato's sales team runs a head-to-head deal contest each quarter. Two reps compete per restaurant deal, and only one wins it. Given the raw contest results, build the full leaderboard showing each rep's deals played, won, lost, and points (2 per win). The catch: a rep can appear as either rep_1 or rep_2 — both columns must be counted.

-- UNION ALL gives each rep both sides of every deal
WITH all_deals AS (
  SELECT rep_1 AS rep,
         CASE WHEN winner = rep_1 THEN 1 ELSE 0 END AS win
  FROM deal_contest
  UNION ALL
  SELECT rep_2 AS rep,
         CASE WHEN winner = rep_2 THEN 1 ELSE 0 END AS win
  FROM deal_contest
)
SELECT
  rep,
  COUNT(*)              AS played,
  SUM(win)              AS won,
  COUNT(*) - SUM(win)  AS lost,
  SUM(win) * 2          AS points
FROM all_deals
GROUP BY rep
ORDER BY points DESC, won DESC;

Group the orders table by status and count how many orders fall into each group. Sort the results so the most common status appears first.

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

Return the 5 employees with the highest salaries. If two employees share the same salary, order them alphabetically by name.

SELECT emp_id, name, department, salary
FROM employees
ORDER BY salary DESC, name ASC
LIMIT 5;

Find all departments where the average salary exceeds $90,000. Return department name and average salary rounded to 2 decimal places. Many candidates fail this by using WHERE instead of HAVING.

SELECT department,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 90000
ORDER BY avg_salary DESC;

Find products that had zero sales transactions in 2025. The trick: put the year filter in the ON clause, not WHERE — otherwise you accidentally convert the LEFT JOIN to an INNER JOIN.

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN sales s
       ON p.product_id = s.product_id
      AND strftime('%Y', s.sale_date) = '2025'
WHERE s.sale_id IS NULL;

Calculate the total revenue per month for 2025. Use strftime to extract the month number and sort results chronologically.

SELECT strftime('%m', sale_date) AS month_num,
       ROUND(SUM(revenue), 2)     AS total_revenue
FROM sales
WHERE strftime('%Y', sale_date) = '2025'
GROUP BY month_num
ORDER BY month_num;

Find all employees who earn a higher salary than their direct manager. The key insight: join the employees table to itself using the manager_id foreign key.

SELECT e.name AS employee_name,
       e.salary AS employee_salary,
       m.name   AS manager_name,
       m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Find the second-highest salary within each department without using window functions. Use a self-join: pair each employee with every higher-paid colleague in the same dept, then HAVING COUNT = 1 means exactly one salary is higher.

SELECT e1.department, e1.name, e1.salary AS second_highest
FROM employees e1
JOIN employees e2
     ON  e1.department = e2.department
    AND e1.salary < e2.salary
GROUP BY e1.department, e1.name, e1.salary
HAVING COUNT(DISTINCT e2.salary) = 1;

Pivot monthly revenue so each row is a month and columns show revenue for Electronics, Furniture, and Stationery. Use SUM(CASE WHEN) — the portable approach that works in all SQL dialects.

SELECT strftime('%Y-%m', s.sale_date) AS month,
       SUM(CASE WHEN p.category = 'Electronics' THEN s.revenue ELSE 0 END) AS electronics,
       SUM(CASE WHEN p.category = 'Furniture'   THEN s.revenue ELSE 0 END) AS furniture,
       SUM(CASE WHEN p.category = 'Stationery'  THEN s.revenue ELSE 0 END) AS stationery
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY month
ORDER BY month;

For each age group, calculate the ratio of time spent sending snaps vs. opening snaps. Use NULLIF in the denominator to prevent division-by-zero errors. Real Snapchat/Meta question from DataLemur.

SELECT age_bucket,
       ROUND(
         SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) /
         NULLIF(SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END), 0),
       2) AS send_to_open_ratio
FROM activities
GROUP BY age_bucket
ORDER BY age_bucket;

Calculate a 3-day rolling average of daily revenue. The frame clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW restricts the window to exactly 3 rows. Using RANGE instead of ROWS would include tied dates — a subtle but important difference.

SELECT rev_date, revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY rev_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS rolling_3day_avg
FROM daily_revenue
ORDER BY rev_date;

Find how many users active in a given month were also active the month before. A self-join links each month to the previous one via a date offset. Real Facebook/Meta DataLemur question.

SELECT curr.activity_month,
       COUNT(DISTINCT curr.user_id) AS retained_users
FROM user_activity curr
JOIN user_activity prev
     ON  curr.user_id = prev.user_id
    AND  curr.activity_month = date(prev.activity_month, '+1 month')
GROUP BY curr.activity_month
ORDER BY curr.activity_month;

For each user, retrieve their third transaction chronologically. Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY txn_date) then filter where rn = 3. Classic DataLemur medium question.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY txn_date
  ) AS rn
  FROM transactions
)
SELECT txn_id, user_id, amount, txn_date
FROM ranked
WHERE rn = 3;

Find the Nth highest distinct salary. DENSE_RANK() is the correct approach — if two people share the top salary, the next distinct value ranks 2nd. Using LIMIT/OFFSET would silently skip ties and return a wrong answer.

WITH ranked_salaries AS (
  SELECT DISTINCT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT salary AS nth_highest_salary
FROM ranked_salaries
WHERE rnk = 2;  -- change 2 to any N

Calculate year-over-year revenue growth per category. LAG(revenue) OVER (PARTITION BY category ORDER BY year) fetches last year's value. Use NULLIF to guard against division by zero.

WITH yoy AS (
  SELECT sale_year, category, revenue,
         LAG(revenue) OVER (
           PARTITION BY category ORDER BY sale_year
         ) AS prev_revenue
  FROM yearly_sales
)
SELECT sale_year, category, revenue,
       ROUND(
         100.0 * (revenue - prev_revenue)
               / NULLIF(prev_revenue, 0), 1
       ) AS yoy_growth_pct
FROM yoy
WHERE prev_revenue IS NOT NULL
ORDER BY category, sale_year;

Find each user's continuous active subscription periods (start date, end date, duration). The gaps-and-islands trick: subtracting the row number (as days) from each consecutive active date produces the same constant — revealing each contiguous island.

WITH active_days AS (
  SELECT user_id, status_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY status_date) AS rn
  FROM subscription_status
  WHERE is_active = 1
),
islands AS (
  SELECT user_id, status_date,
         date(status_date, '-' || rn || ' days') AS grp
  FROM active_days
)
SELECT user_id,
       MIN(status_date) AS period_start,
       MAX(status_date) AS period_end,
       COUNT(*) AS duration_days
FROM islands
GROUP BY user_id, grp
ORDER BY user_id, period_start;

Traverse the full org chart from CEO to every employee using a recursive CTE. The anchor selects root nodes (manager_id IS NULL). The recursive part joins each employee to their parent's CTE row, building the hierarchy level and path.

WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id,
         1 AS lvl, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id,
         o.lvl + 1,
         o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, lvl, path
FROM org
ORDER BY lvl, name;

Group user clickstream events into sessions where any gap exceeding 30 minutes starts a new session. Two-CTE pattern: LAG() detects gaps, then SUM(new_session_flag) as a running count assigns the session ID. Real Google/Meta data engineering interview question.

WITH gaps AS (
  SELECT event_id, user_id, event_time,
    CASE
      WHEN (julianday(event_time) -
            julianday(LAG(event_time) OVER (
              PARTITION BY user_id ORDER BY event_time
            ))) * 1440 > 30
      OR  LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
      THEN 1 ELSE 0
    END AS new_session
  FROM clickstream
),
sessions AS (
  SELECT *,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM gaps
)
SELECT user_id, session_id,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

A customer table has duplicates from system retries. Keep only the most recent record per customer. ROW_NUMBER() PARTITION BY customer_id ORDER BY updated_at DESC assigns rank 1 to the freshest row. Note: DENSE_RANK would fail here — tied timestamps would both get rank 1.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customer_records
)
SELECT record_id, customer_id, name, updated_at
FROM ranked
WHERE rn = 1;

Show each transaction with the account balance both before and after it. Three-step pattern: (1) convert debits to negative amounts; (2) running SUM ... ROWS UNBOUNDED PRECEDING for balance-after; (3) LAG() of that value for balance-before.

WITH signed AS (
  SELECT *,
    CASE WHEN txn_type = 'credit' THEN  amount
         WHEN txn_type = 'debit'  THEN -amount
    END AS signed_amt
  FROM account_txns
),
running AS (
  SELECT *,
    SUM(signed_amt) OVER (
      PARTITION BY account_id
      ORDER BY txn_date, txn_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS balance_after
  FROM signed
)
SELECT txn_id, txn_date, amount, txn_type,
  COALESCE(LAG(balance_after) OVER (
    PARTITION BY account_id ORDER BY txn_date, txn_id
  ), 0) AS balance_before,
  balance_after
FROM running
ORDER BY account_id, txn_date, txn_id;

Find what % of users who bought Product A immediately bought Product B next. LEAD(product) retrieves the very next purchase per user. Filter where current = A and check next = B. Real Apple/DataLemur hard question pattern.

WITH next_product AS (
  SELECT customer_id, product AS current_product,
    LEAD(product) OVER (
      PARTITION BY customer_id ORDER BY purchased_at
    ) AS next_product
  FROM purchase_history
)
SELECT ROUND(
  100.0 * COUNT(CASE WHEN next_product = 'AirPods' THEN 1 END) /
  NULLIF(COUNT(*), 0), 1
) AS airpods_followup_pct
FROM next_product
WHERE current_product = 'iPhone';

Write a query to show all records from the students table. The asterisk * is a wildcard meaning "all columns". This is the very first query every SQL learner writes.

SELECT *
FROM students;

Retrieve only the name and price columns from the products table. In real systems, selecting specific columns is faster than SELECT * because less data travels over the network.

SELECT name, price
FROM products;

Show only employees who work in the Engineering department. The WHERE clause filters rows before they are returned — think of it as the "row filter" in SQL.

SELECT name, salary
FROM employees
WHERE department = 'Engineering';

Retrieve all products and sort them by price from cheapest to most expensive. ORDER BY col ASC is ascending (smallest first); DESC flips it. Without ORDER BY, SQL gives back rows in no guaranteed order.

SELECT name, price
FROM products
ORDER BY price ASC;

Return the 3 students with the highest exam scores. Always pair LIMIT with ORDER BY — otherwise you get a random 3 rows, not the top 3.

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 3;

Find the total number of orders in the orders table. COUNT(*) is the most common aggregate function — it counts every row regardless of NULLs. Adding AS total_orders gives the column a readable name.

SELECT COUNT(*) AS total_orders
FROM orders;

Find the average salary of all employees. AVG(salary) adds up every salary and divides by the number of rows. Use ROUND(AVG(salary), 0) to remove decimal places when you want a clean number.

SELECT ROUND(AVG(salary), 0) AS avg_salary
FROM employees;

Show how many employees are in each department. GROUP BY department creates one row per unique department value, then COUNT(*) counts the employees inside each group. This is the most asked beginner aggregate question.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

Your analytics team at Flipkart notices that a small group of products drives the majority of revenue. Following the Pareto (80/20) rule, write a SQL query to find all products whose combined revenue — when ranked from highest to lowest — accounts for the first 80% of total revenue. Include the cumulative revenue percentage in the result. This pattern is one of the most frequently asked business analytics questions in SQL interviews.

-- Running cumulative SUM to pinpoint the 80% revenue threshold
WITH ranked AS (
  SELECT
    product_id, product_name, revenue,
    SUM(revenue) OVER (
      ORDER BY revenue DESC
      ROWS UNBOUNDED PRECEDING
    ) AS running_total,
    SUM(revenue) OVER () AS total_revenue
  FROM products
)
SELECT
  product_id, product_name, revenue,
  ROUND(running_total * 100.0 / total_revenue, 1) AS cumulative_pct
FROM ranked
WHERE running_total - revenue < total_revenue * 0.8;

-- NTILE(5) splits into 5 equal buckets; bucket 1 = top 20% of rows
SELECT product_id, product_name, revenue
FROM (
  SELECT product_id, product_name, revenue,
    NTILE(5) OVER (ORDER BY revenue DESC) AS quintile
  FROM products
) t
WHERE quintile = 1;

-- PERCENT_RANK = 0 for the top row; ≤ 0.2 means top 20% of products
SELECT product_id, product_name, revenue
FROM (
  SELECT product_id, product_name, revenue,
    PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pct_rank
  FROM products
) t
WHERE pct_rank <= 0.2;

Swiggy's growth team needs a daily customer classification report. For each order date, determine whether each order belongs to a new customer (placing their very first order on that day) or a repeat customer (who ordered at least once before). Output each date with the count of new and repeat customers side by side. This is one of the most common business analytics questions in data analyst interviews — it tests your ability to break a problem into steps using CTEs and conditional aggregation.

-- Step 1: find each customer's first ever order date
WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders
  GROUP BY customer_id
)
-- Step 2: join back, classify, then aggregate per day
SELECT
  o.order_date,
  SUM(CASE WHEN o.order_date = f.first_date THEN 1 ELSE 0 END) AS new_customers,
  SUM(CASE WHEN o.order_date > f.first_date THEN 1 ELSE 0 END) AS repeat_customers
FROM orders o
JOIN first_order f ON o.customer_id = f.customer_id
GROUP BY o.order_date
ORDER BY o.order_date;

-- MIN() OVER (PARTITION BY customer_id) gives first date without a separate GROUP BY
WITH classified AS (
  SELECT
    customer_id, order_date,
    MIN(order_date) OVER (PARTITION BY customer_id) AS first_date
  FROM orders
)
SELECT
  order_date,
  SUM(CASE WHEN order_date = first_date THEN 1 ELSE 0 END) AS new_customers,
  SUM(CASE WHEN order_date > first_date THEN 1 ELSE 0 END) AS repeat_customers
FROM classified
GROUP BY order_date
ORDER BY order_date;

-- CTE 1: tag every order as new or repeat
WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders GROUP BY customer_id
),
tagged AS (
  SELECT o.order_date,
    CASE WHEN o.order_date = f.first_date THEN 'new' ELSE 'repeat' END AS cust_type
  FROM orders o
  JOIN first_order f ON o.customer_id = f.customer_id
)
-- CTE 2: count each type per day
SELECT order_date,
  SUM(CASE WHEN cust_type = 'new'    THEN 1 ELSE 0 END) AS new_customers,
  SUM(CASE WHEN cust_type = 'repeat' THEN 1 ELSE 0 END) AS repeat_customers
FROM tagged
GROUP BY order_date ORDER BY order_date;

Smartworks, a co-working space provider, logs every member check-in: which floor they visit and which resource (laptop station, desktop, monitor, etc.) they use. The analytics team wants a member-level summary report — total check-ins, the floor each member visits most often (the statistical mode, not the maximum), and every distinct resource they have ever used, as a comma-separated list. This question is a staple in product-company interviews because it requires three distinct SQL techniques working together: plain aggregation, window-based mode calculation (SQL has no MODE() function), and string aggregation with deduplication.

WITH visit_count AS (
  SELECT member, COUNT(*) AS total_visits
  FROM cowork_visits
  GROUP BY member
),
floor_mode AS (
  -- rank floors by visit frequency; rn=1 is the most visited
  SELECT member, floor,
    ROW_NUMBER() OVER (
      PARTITION BY member
      ORDER BY COUNT(*) DESC
    ) AS rn
  FROM cowork_visits
  GROUP BY member, floor
),
resource_list AS (
  SELECT member,
    GROUP_CONCAT(DISTINCT resource) AS resources
  FROM cowork_visits
  GROUP BY member
)
SELECT
  v.member, v.total_visits,
  f.floor   AS top_floor,
  r.resources
FROM visit_count v
JOIN floor_mode f
  ON v.member = f.member AND f.rn = 1
JOIN resource_list r
  ON v.member = r.member
ORDER BY v.member;

WITH floor_mode AS (
  SELECT member, floor,
    ROW_NUMBER() OVER (
      PARTITION BY member
      ORDER BY COUNT(*) DESC
    ) AS rn
  FROM cowork_visits
  GROUP BY member, floor
),
summary AS (
  SELECT member,
    COUNT(*) AS total_visits,
    STRING_AGG(DISTINCT resource, ','
      ORDER BY resource) AS resources
  FROM cowork_visits
  GROUP BY member
)
SELECT
  s.member, s.total_visits,
  f.floor AS top_floor,
  s.resources
FROM summary s
JOIN floor_mode f
  ON s.member = f.member AND f.rn = 1
ORDER BY s.member;

-- GROUP_CONCAT for MySQL and SQLite (no STRING_AGG support)
WITH floor_mode AS (
  SELECT member, floor,
    ROW_NUMBER() OVER (
      PARTITION BY member
      ORDER BY COUNT(*) DESC
    ) AS rn
  FROM cowork_visits
  GROUP BY member, floor
),
summary AS (
  SELECT member,
    COUNT(*) AS total_visits,
    GROUP_CONCAT(DISTINCT resource) AS resources
  FROM cowork_visits
  GROUP BY member
)
SELECT
  s.member, s.total_visits,
  f.floor AS top_floor,
  s.resources
FROM summary s
JOIN floor_mode f
  ON s.member = f.member AND f.rn = 1
ORDER BY s.member;

Razorpay's SRE team runs a daily health check on their payment gateway. Each day is logged as up (all systems operational) or down (incident detected). For SLA reporting and incident post-mortems, the team needs a condensed view: each contiguous window of the same status collapsed into a single row with its start date, end date, and status. This is the classic Gaps and Islands problem — SQL has no built-in way to detect "consecutive same-value runs", so you need a window-function trick to assign each island a unique group key.

WITH grp AS (
  SELECT check_date, status,
    ROW_NUMBER() OVER (ORDER BY check_date) -
    ROW_NUMBER() OVER (
      PARTITION BY status
      ORDER BY check_date
    ) AS grp_id
  FROM gateway_log
)
SELECT
  MIN(check_date) AS start_date,
  MAX(check_date) AS end_date,
  status
FROM grp
GROUP BY grp_id, status
ORDER BY start_date;

WITH changes AS (
  SELECT check_date, status,
    CASE WHEN LAG(status)
      OVER (ORDER BY check_date) = status
      THEN 0 ELSE 1 END AS is_start
  FROM gateway_log
),
grps AS (
  SELECT check_date, status,
    SUM(is_start) OVER
      (ORDER BY check_date) AS grp_id
  FROM changes
)
SELECT
  MIN(check_date) AS start_date,
  MAX(check_date) AS end_date,
  status
FROM grps
GROUP BY grp_id, status
ORDER BY start_date;

-- SQL Server uses DATEADD(day, -rn, date); SQLite uses DATE(date, '-N days')
WITH grp AS (
  SELECT check_date, status,
    DATE(check_date,
      '-' || ROW_NUMBER() OVER (
        PARTITION BY status
        ORDER BY check_date
      ) || ' days') AS grp_key
  FROM gateway_log
)
SELECT
  MIN(check_date) AS start_date,
  MAX(check_date) AS end_date,
  status
FROM grp
GROUP BY grp_key, status
ORDER BY start_date;

A Flipkart data team is auditing a JOIN before writing a pipeline. Table t1 has 3 rows and t2 has 3 rows — yet id=1 is duplicated in t1. A junior engineer assumes INNER JOIN returns 3 rows since both tables have 3 rows. How many rows does t1 INNER JOIN t2 ON t1.id = t2.id actually return — and why does the duplicate key matter?

-- Key 1: 2 rows in t1 × 1 row in t2 = 2 rows
-- Key 2: 1 row in t1 × 1 row in t2 = 1 row
-- Key 3: no match in t1 → 0 rows
-- Total: 3 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

Amazon's data team joins two tables before a nightly aggregation. Both tables have duplicate entries for the same key value. A classic interview trap: before running the query, predict the exact row count. Most candidates guess 3 or 4 (min or max of the input table sizes) — both are wrong. The actual result shocks them.

-- Key 1: 2 rows in t1 × 3 rows in t2 = 6 rows  ← Cartesian explosion!
-- Key 2: no match in t2 → 0 rows
-- Key 3: no match in t1 → 0 rows
-- Total: 6 rows (larger than both input tables!)
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

Same tables as Q55, but switching to LEFT JOIN. LEFT JOIN guarantees every row in t1 appears in the output — matched or not. t1's key=2 has no match in t2. How many rows does the LEFT JOIN return? Compare to Q55's INNER JOIN answer of 6.

-- INNER part — key=1: 2 × 3 = 6 rows
-- LEFT-only — key=2 in t1, no match in t2: 1 row (t2_id = NULL)
-- key=3 is only in t2 → LEFT JOIN does not preserve it
-- Total: 6 + 1 = 7 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;

Same tables, now RIGHT JOIN. Every t2 row must appear — matched or not. t2's key=3 has no match in t1 and gets NULL for t1.id. But t1's key=2 (unmatched) is now dropped. Notice: LEFT JOIN and RIGHT JOIN both return 7 rows here, but they preserve different unmatched keys.

-- INNER part — key=1: 2 × 3 = 6 rows
-- RIGHT-only — key=3 in t2, no match in t1: 1 row (t1_id = NULL)
-- key=2 is only in t1 → RIGHT JOIN drops it (LEFT JOIN would keep it)
-- Total: 6 + 1 = 7 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
RIGHT JOIN t2 ON t1.id = t2.id;
-- SQLite 3.39+ supports RIGHT JOIN natively
-- Equivalent: FROM t2 LEFT JOIN t1 ON t2.id = t1.id

The definitive JOIN interview question. Given t1 = [1,1,2,3] and t2 = [1,1,2,4], predict the row count for all four JOIN types. Keys 1 and 2 are shared; key 3 is exclusive to t1; key 4 is exclusive to t2. Master the formula INNER ≤ LEFT, INNER ≤ RIGHT, max(LEFT, RIGHT) ≤ FULL — this appears in senior data engineering interviews at every major tech company.

-- Key 1: 2 (t1) × 2 (t2) = 4 rows
-- Key 2: 1 (t1) × 1 (t2) = 1 row
-- Key 3: only in t1 → 0 rows  |  Key 4: only in t2 → 0 rows
-- INNER JOIN total: 5 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

-- Results: INNER=5, LEFT=6, RIGHT=6, FULL OUTER=7
SELECT 'INNER JOIN' AS join_type,
       COUNT(*) AS row_count
FROM t1 INNER JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT 'LEFT JOIN', COUNT(*)
FROM t1 LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT 'RIGHT JOIN', COUNT(*)
FROM t2 LEFT JOIN t1 ON t2.id = t1.id
UNION ALL
SELECT 'FULL OUTER', COUNT(*) FROM (
  SELECT t1.id, t2.id
  FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  UNION ALL
  SELECT t1.id, t2.id
  FROM t2 LEFT JOIN t1 ON t2.id = t1.id
  WHERE t1.id IS NULL
);

Zomato's data team joins an orders table (t1) with a customers table (t2). Both tables have a NULL key row — unassigned records. A junior engineer expects the two NULLs to pair up in the INNER JOIN. They won't. SQL uses three-valued logic: the ON clause keeps only rows where the condition is TRUE. NULL = NULL is UNKNOWN — not TRUE — so NULL keys are invisible to INNER JOIN and appear only as unmatched rows in LEFT/RIGHT JOIN.

-- NULL = NULL → UNKNOWN → treated as no match
-- Key 1: 1 × 1 = 1 row
-- id=2 (t1): no t2 match → 0 rows
-- id=NULL (t1): NULL = NULL is UNKNOWN → 0 rows
-- id=NULL (t2): same — never matches any t1 row
-- Total INNER: 1 row
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

-- LEFT JOIN output:
-- (1, 1)     ← matched
-- (2, NULL)  ← t1.id=2 unmatched, t2 side is NULL
-- (NULL, NULL) ← t1.id=NULL unmatched (NULL key never matches)
-- Total LEFT: 3 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;
-- To force NULL = NULL to match, use IS (SQLite NULL-safe equals):
-- INNER JOIN t2 ON t1.id IS t2.id  → 2 rows: (1,1) and (NULL,NULL)

Swiggy's data team finds product IDs contain both NULL entries (missing data) and empty string entries (data entry errors). t1 has key '1', key '', and key NULL. t2 has key '1', key '', and key '2'. In most UIs both NULL and '' display as blank — but SQL treats them completely differently. Predict the INNER JOIN row count and which rows appear.

-- '' = '' is TRUE  → empty strings match each other!
-- NULL = '' is UNKNOWN → NULL key never matches
-- Key '1': 1 × 1 = 1 row
-- Key '' : 1 × 1 = 1 row  ← empty string IS a matchable value
-- Key NULL (t1): NULL = anything → UNKNOWN → 0 rows
-- Total INNER: 2 rows
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

-- Standard ON t1.id = t2.id → 2 rows (NULL never matches)
-- NULL-safe ON t1.id IS t2.id → 2 rows (no NULL in t2 to pair with)
-- To see NULL-safe behavior, add NULL to t2 and compare:
-- INSERT INTO t2 VALUES(NULL);
-- ON t1.id = t2.id   → still 2 rows (NULL = NULL = UNKNOWN)
-- ON t1.id IS t2.id  → 3 rows (NULL IS NULL = TRUE in SQLite)
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
INNER JOIN t2 ON t1.id IS t2.id;
-- PostgreSQL equivalent: ON t1.id IS NOT DISTINCT FROM t2.id