Ask the database, get the answer.

Lesson 2 of 6About 10 minutes5 steps

Most of an analyst’s questions are a JOIN, a GROUP BY and a filter. Here they are on a small shop’s real tables, run on PostgreSQL, with the traps interviewers love: missing matches, NULLs and ties.

  • Choose between INNER and LEFT JOIN
  • Group, filter groups and rank within them
  • Count correctly around NULLs

You need: Lesson 1, or a little SQL.

  1. 1Statistics you use every day
  2. 2SQL that answers questions
  3. 3Charts that answer the question
  4. 4Power BI’s filter context
  5. 5A/B tests
  6. 6Guesstimates
0 of 5 steps

  1. JOIN: who matches

    Predict first

    The shop has 7 customers and 11 orders. An INNER JOIN of the two returns how many rows?

    Just show me

    11: one row for each order with a matching customer. Customers with no orders vanish.

    An INNER JOIN keeps only the rows that match on both sides. That’s right for “each order with its customer’s name”, and wrong for “every customer and what they bought”, because customers who never ordered disappear without a warning.

    SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id
ORDER BY o.order_id
    nameorder_idamount
    Aarav Sharma10011798.00
    Diya Patel10022499.00
    Aarav Sharma10031999.00
    Kabir Singh10043398.00
    Meera Iyer10051299.00

    The first 5 of 11 rows, as PostgreSQL returned them

    Quick check

    You want every customer, including those who never ordered. Which join?

    A LEFT JOIN keeps every row of the left table and fills NULLs where nothing matches.

  2. LEFT JOIN: who’s missing

    Predict first

    How many of the 7 customers never ordered?

    Just show me

    2: Aarav Sharma and Sana Khan. That Aarav Sharma is a second one, with the same name and the same email typed in capitals: a duplicate customer.

    LEFT JOIN, count the orders, and the customers with none appear. Count o.order_id, not *: COUNT(*) would count the empty match as an order. And the duplicate is a classic: real tables are full of the same person entered twice, which splits their history in two.

    SELECT c.name,
  COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name
    nameorders
    Diya Patel4
    Aarav Sharma3
    Kabir Singh2
    Meera Iyer1
    Rohan Das1
    Aarav Sharma0
    Sana Khan0

    Seven rows, as PostgreSQL returned them

    Run these yourself in the SQL Lab

    Quick check

    Why COUNT(o.order_id) and not COUNT(*) after a LEFT JOIN?

    COUNT(column) skips NULLs; COUNT(*) counts rows, the empty match included.

  3. GROUP BY, then HAVING

    Predict first

    Which payment mode brought in the most delivered revenue?

    Just show me

    UPI: ₹10,591 from 6 orders, against ₹3,998 by card and ₹2,499 cash on delivery.

    WHERE filters rows before grouping (here, delivered orders only); GROUP BY makes one row per group; HAVING filters the groups afterwards. “Customers with 2 or more orders” is HAVING COUNT(*) >= 2, which finds 3 customers here. Mixing up WHERE and HAVING is one of the most-asked SQL interview questions.

    SELECT payment_mode,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY payment_mode
ORDER BY revenue DESC
    payment_modeordersrevenue
    UPI610591.00
    Card23998.00
    COD12499.00

    Three rows, as PostgreSQL returned them

    Quick check

    Cities with more than 100 orders: where does “more than 100” go?

    It’s a condition on each group’s count, so it filters groups: HAVING.

  4. Ranking within groups

    Predict first

    In Analytics, two people earn the same. With RANK(), what rank comes after their shared 3rd?

    Just show me

    5: Priya Nair and Vikram Joshi share 3rd, so RANK skips 4. DENSE_RANK would give 4, and ROW_NUMBER would break the tie.

    A window function computes across related rows without collapsing them: PARTITION BY says which rows belong together, ORDER BY sets the order inside. The top 3 earners in each department, each customer’s latest order and a running total are all window functions.

    SELECT name, department, salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rnk
FROM employees
ORDER BY department, rnk
    namedepartmentsalaryrnk
    Nisha RaoAnalytics32000001
    Arjun MehtaAnalytics18000002
    Priya NairAnalytics15000003
    Vikram JoshiAnalytics15000003
    Farah AliAnalytics12000005
    Kunal VermaOperations16000001
    Ishaan GuptaOperations14000002
    Tara BoseOperations11000003

    Eight rows, as PostgreSQL returned them

    Quick check

    You want exactly one row per customer: their latest order. Which function?

    ROW_NUMBER never ties, so “= 1” picks exactly one row in each partition.

  5. NULL is not a value

    Predict first

    7 customers. What does COUNT(referred_by) return?

    Just show me

    3: COUNT(column) skips NULLs, and only 3 customers were referred by someone.

    NULL means unknown, so referred_by = NULL is never true: write IS NULL. Aggregates skip NULLs (AVG divides by the non-NULL count only), and COALESCE fills them when a default makes sense. Most wrong totals in SQL come from forgetting this.

    SELECT COUNT(*) AS all_rows,
  COUNT(referred_by) AS referred
FROM customers
    all_rowsreferred
    73

    One row, as PostgreSQL returned it

    Quick check

    WHERE referred_by = NULL returns nothing. The fix?

    A comparison with NULL is unknown, never true; IS NULL is the test.

In the interview: SQL that answers questions.

Ask Prism about this lesson

Sources

  1. The SQL guide’s example shop, every query here run on PostgreSQL (PGlite, the SQL Lab’s engine) on 2 Nov 2026.
  2. PostgreSQL documentation, “Window Functions”.

Back: lesson 1, Statistics you use every day