Scenario-Based SQL Interview Questions

import duckdb

db = duckdb.connect()
db.execute("INSTALL excel; LOAD excel")
db.execute("""CREATE TABLE raw AS SELECT * FROM read_xlsx('Online Retail.xlsx', all_varchar = true)""")   # UCI Online Retail
db.execute("""CREATE TABLE lines AS SELECT
    InvoiceNo AS invoice, StockCode AS product, TRIM(Description) AS description,
    CAST(Quantity AS INTEGER) AS qty, ROUND(CAST(UnitPrice AS DOUBLE), 2) AS price,
    CAST(CAST(CustomerID AS DOUBLE) AS INTEGER) AS customer, Country AS country,
    TIMESTAMP '1899-12-30' + to_seconds(CAST(ROUND(CAST(InvoiceDate AS DOUBLE) * 86400) AS BIGINT)) AS ts  -- Excel serial date
    FROM raw""")


def ask(title, sql):
    print(f"-- {title}")
    for row in db.execute(sql).fetchall():
        print("  ", *row)


ask("S1 the data first: rows, date range, exact duplicate lines, cancellations, lines without a customer", """
    SELECT COUNT(*), MIN(ts)::DATE, MAX(ts)::DATE,
           COUNT(*) - (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM lines)) AS duplicate_lines,
           COUNT(*) FILTER (WHERE invoice LIKE 'C%') AS cancellation_lines,
           COUNT(*) FILTER (WHERE customer IS NULL) AS no_customer
    FROM lines""")

db.execute("""CREATE TABLE sales AS SELECT DISTINCT * FROM lines
              WHERE customer IS NOT NULL AND invoice NOT LIKE 'C%' AND qty > 0 AND price > 0""")

ask("S2 monthly revenue: does the last month really collapse?", """
    SELECT strftime(ts, '%Y-%m') AS month, ROUND(SUM(qty * price)) AS revenue,
           COUNT(DISTINCT ts::DATE) AS trading_days, MAX(ts)::DATE AS last_day
    FROM sales WHERE ts >= '2011-09-01' GROUP BY month ORDER BY month""")

ask("S3 customers who bought in the first half of 2011 but not the second", """
    SELECT COUNT(DISTINCT customer) FROM sales a
    WHERE ts >= '2011-01-01' AND ts < '2011-07-01'
      AND NOT EXISTS (SELECT 1 FROM sales b WHERE b.customer = a.customer AND b.ts >= '2011-07-01')""")

ask("S4 each customer's latest order (one row per customer)", """
    WITH orders AS (SELECT customer, invoice, MIN(ts) AS ts, ROUND(SUM(qty * price), 2) AS value
                    FROM sales GROUP BY customer, invoice),
         ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY ts DESC, invoice DESC) AS n FROM orders)
    SELECT COUNT(*) AS customers, COUNT(*) FILTER (WHERE ts < '2011-09-01') AS last_order_before_september
    FROM ranked WHERE n = 1""")

ask("S5 cohort: customers whose first order was in December 2010, still buying N months later", """
    WITH firsts AS (SELECT customer, MIN(ts) AS first_ts FROM sales GROUP BY customer),
         cohort AS (SELECT customer FROM firsts WHERE first_ts >= '2010-12-01' AND first_ts < '2011-01-01'),
         active AS (SELECT DISTINCT s.customer, date_diff('month', DATE '2010-12-01', s.ts::DATE) AS months_later
                    FROM sales s JOIN cohort USING (customer))
    SELECT months_later, COUNT(*) AS customers,
           ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM cohort), 1) AS pct_of_cohort
    FROM active WHERE months_later IN (0, 1, 3, 6, 11) GROUP BY months_later ORDER BY months_later""")

ask("S6 repeat customers: share with more than one order", """
    SELECT COUNT(*) AS customers, ROUND(100.0 * AVG(CASE WHEN orders > 1 THEN 1 ELSE 0 END), 1) AS pct_repeat
    FROM (SELECT customer, COUNT(DISTINCT invoice) AS orders FROM sales GROUP BY customer)""")

ask("S7 median and mean order value (one outlier is enough to separate them)", """
    WITH orders AS (SELECT invoice, SUM(qty * price) AS value FROM sales GROUP BY invoice)
    SELECT ROUND(MEDIAN(value), 2) AS median, ROUND(AVG(value), 2) AS mean, ROUND(MAX(value), 2) AS largest FROM orders""")

ask("   the largest order, and any cancellation of the same product by the same customer", """
    WITH biggest AS (SELECT invoice, customer, product, qty, ts FROM sales
                     WHERE invoice = (SELECT invoice FROM sales GROUP BY invoice ORDER BY SUM(qty * price) DESC LIMIT 1))
    SELECT b.invoice, b.ts, b.qty, c.invoice AS cancelled_by, c.ts AS cancelled_at, c.qty
    FROM biggest b LEFT JOIN lines c
      ON c.customer = b.customer AND c.product = b.product AND c.invoice LIKE 'C%' AND c.qty = -b.qty""")

ask("S8 products most often bought together (pairs in the same order)", """
    WITH items AS (SELECT DISTINCT invoice, description FROM sales)
    SELECT a.description, b.description, COUNT(*) AS orders
    FROM items a JOIN items b ON a.invoice = b.invoice AND a.description < b.description
    GROUP BY ALL ORDER BY orders DESC, a.description LIMIT 3""")

-- S1 the data first: rows, date range, exact duplicate lines, cancellations, lines without a customer
   541909 2010-12-01 2011-12-09 5268 9288 135080
-- S2 monthly revenue: does the last month really collapse?
   2011-09 950690.0 26 2011-09-30
   2011-10 1035642.0 26 2011-10-31
   2011-11 1156206.0 26 2011-11-30
   2011-12 517190.0 8 2011-12-09
-- S3 customers who bought in the first half of 2011 but not the second
   853
-- S4 each customer's latest order (one row per customer)
   4338 1365
-- S5 cohort: customers whose first order was in December 2010, still buying N months later
   0 885 100.0
   1 324 36.6
   3 340 38.4
   6 321 36.3
   11 445 50.3
-- S6 repeat customers: share with more than one order
   4338 65.6
-- S7 median and mean order value (one outlier is enough to separate them)
   302.58 479.56 168469.6
--    the largest order, and any cancellation of the same product by the same customer
   581483 2011-12-09 09:15:00 80995 C581484 2011-12-09 09:27:00 -80995
-- S8 products most often bought together (pairs in the same order)
   JUMBO BAG PINK POLKADOT JUMBO BAG RED RETROSPOT 546
   GREEN REGENCY TEACUP AND SAUCER ROSES REGENCY TEACUP AND SAUCER 541
   ALARM CLOCK BAKELIKE GREEN ALARM CLOCK BAKELIKE RED 530