16 SQL problems that decide your offer

Easy: Warm up. 2 min each.

1. 01

   2nd highest salary

   Show my answer

   SELECT MAX(salary) FROM employees
   WHERE salary < (SELECT MAX(salary)
                   FROM employees);

2. 02

   Employees earning more than their manager

   Show my answer

   SELECT e.name
   FROM employees e
   JOIN employees m
     ON e.manager_id = m.emp_id
   WHERE e.salary > m.salary;

3. 03

   Duplicate emails

   Show my answer

   SELECT email, COUNT(*)
   FROM users
   GROUP BY email
   HAVING COUNT(*) > 1;

4. 04

   Customers who never ordered

   Show my answer

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

5. 05

   Each customer's first order date

   Show my answer

   SELECT customer_id, MIN(order_date)
   FROM orders
   GROUP BY customer_id;

6. 06

   Repeat customers

   Show my answer

   SELECT customer_id
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1;

Medium: Now it gets real.

1. 07

   Highest paid in each department

   Show my answer

   SELECT dept, name, salary
   FROM employees
   WHERE (dept, salary) IN (
     SELECT dept, MAX(salary)
     FROM employees GROUP BY dept);

2. 08

   Departments above company average

   Show my answer

   SELECT dept, AVG(salary)
   FROM employees GROUP BY dept
   HAVING AVG(salary) >
     (SELECT AVG(salary) FROM employees);

3. 09

   Running total of revenue

   Show my answer

   SELECT order_date, amount,
     SUM(amount) OVER (
       ORDER BY order_date) AS running_total
   FROM orders;

4. 10

   % share of total revenue

   Show my answer

   SELECT customer_id,
     SUM(amount) * 100.0 /
     SUM(SUM(amount)) OVER () AS pct
   FROM orders GROUP BY customer_id;

Hard: The offer round.

1. 11

   3rd highest salary per department

   Show my answer

   WITH r AS (
     SELECT name, dept, salary,
       DENSE_RANK() OVER (
         PARTITION BY dept
         ORDER BY salary DESC) AS rk
     FROM employees)
   SELECT * FROM r WHERE rk = 3;

2. 12

   Top 3 products per category

   Show my answer

   WITH r AS (
     SELECT category, product, sales,
       ROW_NUMBER() OVER (
         PARTITION BY category
         ORDER BY sales DESC) AS rn
     FROM products)
   SELECT * FROM r WHERE rn <= 3;

3. 13

   Month-over-month revenue change

   Show my answer

   WITH m AS (
     SELECT DATE_TRUNC('month', order_date) mth,
            SUM(amount) rev
     FROM orders GROUP BY 1)
   SELECT mth, rev,
     rev - LAG(rev) OVER (ORDER BY mth) chg
   FROM m;

4. 14

   Pivot salary by department

   Show my answer

   SELECT
     SUM(CASE WHEN dept = 'Sales'
         THEN salary END) AS sales,
     SUM(CASE WHEN dept = 'HR'
         THEN salary END) AS hr
   FROM employees;

5. 15

   7-day moving average

   Show my answer

   SELECT day, rev,
     AVG(rev) OVER (
       ORDER BY day
       ROWS BETWEEN 6 PRECEDING
       AND CURRENT ROW) AS ma7
   FROM daily_sales;

6. 16

   Joined in the last 90 days

   Show my answer

   SELECT name, join_date
   FROM employees
   WHERE join_date >=
     CURRENT_DATE - INTERVAL '90 days';

How to actually use this