THE 10 SQL QUESTIONS EVERY
DATA INTERVIEW WILL ASK

CopySELECT
  category,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS num_orders,
  AVG(revenue) AS avg_order_value
FROM orders
GROUP BY category
ORDER BY total_revenue DESC;

CopySELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS total_orders,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

CopySELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

Copy-- Step 1: Calculate total spend per customer
WITH customer_spend AS (
  SELECT
    customer_id,
    SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
),

-- Step 2: Calculate the average spend across all customers
avg_spend AS (
  SELECT AVG(total_spend) AS avg_total
  FROM customer_spend
)

-- Step 3: Filter customers above average
SELECT
  cs.customer_id,
  cs.total_spend,
  ROUND(a.avg_total, 2) AS avg_spend
FROM customer_spend cs
CROSS JOIN avg_spend a
WHERE cs.total_spend > a.avg_total
ORDER BY cs.total_spend DESC;

CopySELECT name
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

CopySELECT
  order_id,
  customer_id,
  amount,
  CASE
    WHEN amount < 50  THEN 'Small'
    WHEN amount < 200 THEN 'Medium'
    ELSE 'Large'
  END AS order_size
FROM orders;

CopySELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS num_orders
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY 1
ORDER BY 1;

Copy-- ❌ WRONG — can't filter on COUNT in WHERE
SELECT customer_id, COUNT(*) AS num_orders
FROM orders
WHERE COUNT(*) > 5  -- this will throw an error
GROUP BY customer_id;

-- ✅ CORRECT — use HAVING to filter after aggregation
SELECT customer_id, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY num_orders DESC;

CopyWITH ranked_products AS (
  SELECT
    category,
    product_name,
    SUM(revenue) AS total_revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY SUM(revenue) DESC
    ) AS rank
  FROM sales
  GROUP BY category, product_name
)
SELECT category, product_name, total_revenue
FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank;

CopySELECT
  email,
  COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

CopyWITH deduped AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC  -- keep most recent
    ) AS rn
  FROM customers
)
SELECT * FROM deduped
WHERE rn = 1;  -- only keep the first row per email