SQL Patterns

SELECT user_id, event_time,
SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM (
    SELECT user_id, event_time,
    CASE
        WHEN DATEDIFF(minute, LAG(event_time) OVER
        (PARTITION BY user_id ORDER BY event_time), event_time) > 30
        THEN 1
        ELSE 0
    END AS is_new
    FROM events
) t;

SELECT DISTINCT customer_id
FROM (
    SELECT customer_id, month, amount,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY month) AS prev_amt
    FROM spend
) t
WHERE amount > prev_amt;

SELECT DISTINCT customer_id
FROM (
    SELECT customer_id, order_date,
    LAG(order_date) OVER
    (PARTITION BY customer_id ORDER BY order_date) AS prev_date
    FROM orders
) t
WHERE DATEDIFF(day, prev_date, order_date) = 1;

SELECT DISTINCT a.customer_id
FROM activity a
WHERE MONTH(a.activity_date) = MONTH(CURDATE())
AND YEAR(a.activity_date) = YEAR(CURDATE())
AND a.customer_id NOT IN (
    SELECT customer_id
    FROM activity
    WHERE MONTH(activity_date) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))
    AND YEAR(activity_date) = YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))
);

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) =
(SELECT COUNT(DISTINCT MONTH(order_date)) FROM orders);

SELECT o1.id + 1 AS missing_id
FROM orders o1
LEFT JOIN orders o2 ON o1.id + 1 = o2.id
WHERE o2.id IS NULL
AND o1.id < (SELECT MAX(id) FROM orders);

SELECT user_id
FROM (
    SELECT user_id, login_date,
    DATEADD(day, -ROW_NUMBER() OVER
    (PARTITION BY user_id ORDER BY login_date), login_date) AS grp
    FROM logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

SELECT
category,
SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS pct
FROM sales
GROUP BY category;

SELECT
order_id,
CASE
    WHEN DATEDIFF(day, order_date, delivery_date) <= 2
    THEN 'Fast'
    ELSE 'Slow'
END AS delivery_type
FROM orders;

SELECT
e.name AS emp_name,
m.name AS mgr_name,
sm.name AS sr_mgr_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees sm ON m.manager_id = sm.id;

SELECT o.id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM payments p
    WHERE p.order_id = o.id
);

SELECT
SUM(CASE WHEN month = 'Jan' THEN sales END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN sales END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN sales END) AS Mar
FROM sales;

SELECT a.*
FROM bookings a
JOIN bookings b
ON a.id != b.id
AND a.start_date < b.end_date
AND a.end_date > b.start_date;

SELECT AVG(salary) AS median
FROM (
    SELECT salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS rn,
    COUNT(*) OVER () AS cnt
    FROM employees
) t
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);

SELECT DISTINCT user_id
FROM activity
WHERE month IN ('2024-01', '2024-02')
GROUP BY user_id
HAVING COUNT(DISTINCT month) = 2;

SELECT emp_name FROM (
    SELECT emp_name, salary,
    (SUM(salary) OVER() - SUM(salary) OVER(PARTITION BY dept)) /
    NULLIF(COUNT(*) OVER() - COUNT(*) OVER(PARTITION BY dept), 0) AS out_avg
    FROM employees
) t
WHERE salary > out_avg;

SELECT
user_id,
SUM(CASE WHEN type = 'CR' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY user_id;

SELECT dept, salary FROM (
    SELECT dept, salary,
    DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk <= 2;

SELECT
date,
AVG(revenue) OVER(
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM daily_sales;