Some SQL patterns that are very helpful for interview preparation.

1. Top N Records per Group

DECLARE @N INT = 2;

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY device
               ORDER BY click DESC
           ) rn
    FROM clickstreams
)
SELECT *
FROM cte
WHERE rn = @N;

2. Nth Highest Salary

DECLARE @N INT = 2;

WITH cte AS (
    SELECT salary,
           DENSE_RANK() OVER (
               ORDER BY salary DESC
           ) rn
    FROM employees
)
SELECT *
FROM cte
WHERE rn = @N;
-- Using `DENSE_RANK()` to handle ties.

3. Delete Duplicates

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY id
           ) rn
    FROM customers
)
DELETE
FROM cte
WHERE rn > 1;

4. [IMP] Consecutive Records

Find users/customers who logged in for 3 consecutive days.

WITH cte AS (
    SELECT *,
           LAG(login_date, 1) OVER (
               PARTITION BY customer_id
               ORDER BY login_date
           ) prev_login1,
           LAG(login_date, 2) OVER (
               PARTITION BY customer_id
               ORDER BY login_date
           ) prev_login2
    FROM logins
)
SELECT DISTINCT customer_id
FROM cte
WHERE DATEDIFF(DAY, prev_login1, login_date) = 1
  AND DATEDIFF(DAY, prev_login2, prev_login1) = 1;

5. Gap Between Records

Find the gap between consecutive orders.

SELECT *,
       DATEDIFF(DAY, prev_order, order_date) AS gap
FROM (
    SELECT customer_id,
           order_date,
           LAG(order_date) OVER (
               PARTITION BY customer_id
               ORDER BY order_date
           ) prev_order
    FROM orders
) t;

6. Latest Record per Entity

Frequently asked in SCD Type 2 scenarios.

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_at DESC
           ) rn
    FROM customer_history
)
SELECT *
FROM cte
WHERE rn = 1;

7. Percentage Contribution

SELECT customer_id,
       amount,
       ROUND(
           amount * 100.0 /
           SUM(amount) OVER (),
           2
       ) AS percentage
FROM sales;

8. Cumulative Percentage (Pareto Analysis)

WITH sales_cte AS (
    SELECT product_id,
           revenue,
           SUM(revenue) OVER (
               ORDER BY revenue DESC
           ) cum_revenue,
           SUM(revenue) OVER () total_revenue
    FROM sales
)
SELECT *,
       cum_revenue * 100.0 / total_revenue AS cumulative_percentage
FROM sales_cte;

9. [IMP] Islands and Gaps

Find consecutive date ranges.

WITH cte AS (
    SELECT login_date,
           ROW_NUMBER() OVER (
               ORDER BY login_date
           ) rn
    FROM logins
)
SELECT DATEADD(day, -rn, login_date) AS grp,
       MIN(login_date) AS start_date,
       MAX(login_date) AS end_date
FROM cte
GROUP BY DATEADD(day, -rn, login_date);

10. Pivoting Data

SELECT department,
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS males,
       SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS females
FROM employees
GROUP BY department;

11. Unpivot Data

SELECT employee_id,
       'salary' AS metric,
       salary AS value
FROM employees

UNION ALL

SELECT employee_id,
       'bonus' AS metric,
       bonus AS value
FROM employees;

#de-infinity #week-1