Round 2: SQL (Write the Query)

SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
ORDER BY total_spend DESC
LIMIT 3;

WITH ranked AS (
  SELECT department, employee, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS rev
  FROM orders GROUP BY 1
)
SELECT month, rev,
  ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month))
    / NULLIF(LAG(rev) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly;

SELECT s.user_id
FROM signups s
LEFT JOIN purchases p ON s.user_id = p.user_id
WHERE p.user_id IS NULL;

SELECT campaign_id,
  ROUND(100.0 *
    SUM(CASE WHEN event = 'click' THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event = 'impression' THEN 1 ELSE 0 END), 0)
  , 2) AS ctr
FROM events GROUP BY campaign_id;