Practical SQL Patterns

WITH product_sales AS (
    SELECT
        cat.id   AS cat_id,
        cat.name AS category,
        p.name   AS product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        SUM(oi.quantity)                 AS units_sold
    FROM order_items   AS oi
    JOIN orders         AS o   ON oi.order_id   = o.id
    JOIN products       AS p   ON oi.product_id = p.id
    JOIN categories     AS cat ON p.category_id = cat.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
      AND o.ordered_at >= '2024-01-01'
      AND o.ordered_at <  '2025-01-01'
      AND cat.depth = 2
    GROUP BY cat.id, cat.name, p.id, p.name
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY cat_id
            ORDER BY revenue DESC, units_sold DESC
        ) AS rn
    FROM product_sales
)
SELECT category, product_name,
       ROUND(revenue, 0) AS revenue,
       units_sold,
       rn AS rank
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

SELECT
    cal.date_key,
    cal.day_name,
    cal.is_weekend,
    cal.is_holiday,
    cal.holiday_name
FROM calendar AS cal
LEFT JOIN orders AS o
    ON DATE(o.ordered_at) = cal.date_key
   AND o.status NOT IN ('cancelled')
WHERE cal.date_key BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.id IS NULL
ORDER BY cal.date_key;

SELECT
    SUBSTR(ordered_at, 1, 7) AS year_month,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '1' THEN total_amount ELSE 0 END) AS INTEGER) AS mon,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '2' THEN total_amount ELSE 0 END) AS INTEGER) AS tue,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '3' THEN total_amount ELSE 0 END) AS INTEGER) AS wed,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '4' THEN total_amount ELSE 0 END) AS INTEGER) AS thu,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '5' THEN total_amount ELSE 0 END) AS INTEGER) AS fri,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '6' THEN total_amount ELSE 0 END) AS INTEGER) AS sat,
    CAST(SUM(CASE WHEN STRFTIME('%w', ordered_at) = '0' THEN total_amount ELSE 0 END) AS INTEGER) AS sun
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
  AND status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY year_month;

WITH product_rev AS (
    SELECT
        p.name AS product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items AS oi
    JOIN orders   AS o ON oi.order_id   = o.id
    JOIN products AS p ON oi.product_id = p.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
      AND o.ordered_at >= '2024-01-01'
      AND o.ordered_at <  '2025-01-01'
    GROUP BY p.id, p.name
),
cumulative AS (
    SELECT
        product_name,
        ROUND(revenue, 0) AS revenue,
        ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct,
        ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC)
            / SUM(revenue) OVER (), 2) AS cumulative_pct
    FROM product_rev
)
SELECT product_name, revenue, pct, cumulative_pct
FROM cumulative
WHERE cumulative_pct <= 80
ORDER BY revenue DESC;

SELECT
    SUBSTR(p.paid_at, 1, 7) AS year_month,
    ROUND(100.0 * SUM(CASE WHEN p.method = 'card' THEN p.amount ELSE 0 END)
        / SUM(p.amount), 1) AS card_pct,
    ROUND(100.0 * SUM(CASE WHEN p.method = 'bank_transfer' THEN p.amount ELSE 0 END)
        / SUM(p.amount), 1) AS bank_transfer_pct,
    ROUND(100.0 * SUM(CASE WHEN p.method = 'kakao_pay' THEN p.amount ELSE 0 END)
        / SUM(p.amount), 1) AS kakao_pay_pct,
    ROUND(100.0 * SUM(CASE WHEN p.method = 'naver_pay' THEN p.amount ELSE 0 END)
        / SUM(p.amount), 1) AS naver_pay_pct,
    ROUND(100.0 * SUM(CASE WHEN p.method NOT IN ('card','bank_transfer','kakao_pay','naver_pay')
        THEN p.amount ELSE 0 END) / SUM(p.amount), 1) AS other_pct
FROM payments AS p
WHERE p.status = 'completed'
  AND p.paid_at >= '2024-01-01'
  AND p.paid_at <  '2025-01-01'
GROUP BY SUBSTR(p.paid_at, 1, 7)
ORDER BY year_month;

WITH view_gaps AS (
    SELECT
        customer_id,
        viewed_at,
        CASE
            WHEN (julianday(viewed_at) - julianday(
                LAG(viewed_at) OVER (PARTITION BY customer_id ORDER BY viewed_at)
            )) * 86400 > 1800
            OR LAG(viewed_at) OVER (PARTITION BY customer_id ORDER BY viewed_at) IS NULL
            THEN 1
            ELSE 0
        END AS is_new_session
    FROM product_views
),
sessions AS (
    SELECT
        customer_id,
        viewed_at,
        SUM(is_new_session) OVER (
            PARTITION BY customer_id ORDER BY viewed_at
        ) AS session_id
    FROM view_gaps
),
session_stats AS (
    SELECT
        customer_id,
        session_id,
        COUNT(*) AS view_count,
        ROUND((julianday(MAX(viewed_at)) - julianday(MIN(viewed_at))) * 1440, 1) AS session_minutes
    FROM sessions
    GROUP BY customer_id, session_id
)
SELECT
    customer_id,
    COUNT(*) AS total_sessions,
    ROUND(AVG(view_count), 1) AS avg_views_per_session,
    ROUND(AVG(session_minutes), 1) AS avg_session_minutes
FROM session_stats
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY total_sessions DESC
LIMIT 20;

WITH daily_orders AS (
    SELECT DISTINCT
        customer_id,
        DATE(ordered_at) AS order_date
    FROM orders
    WHERE ordered_at >= '2024-01-01'
      AND ordered_at <  '2025-01-01'
      AND status NOT IN ('cancelled')
),
numbered AS (
    SELECT
        customer_id,
        order_date,
        DATE(order_date, '-' || (ROW_NUMBER() OVER (
            PARTITION BY customer_id ORDER BY order_date
        ) - 1) || ' days') AS grp
    FROM daily_orders
),
streaks AS (
    SELECT
        customer_id,
        grp,
        COUNT(*) AS consecutive_days,
        MIN(order_date) AS streak_start,
        MAX(order_date) AS streak_end
    FROM numbered
    GROUP BY customer_id, grp
    HAVING COUNT(*) >= 2
)
SELECT
    c.name AS customer_name,
    s.consecutive_days,
    s.streak_start,
    s.streak_end
FROM streaks AS s
JOIN customers AS c ON s.customer_id = c.id
ORDER BY s.consecutive_days DESC, s.streak_start
LIMIT 10;

WITH customer_revenue AS (
    SELECT
        c.id,
        c.name AS customer_name,
        SUM(o.total_amount) AS total_spent
    FROM orders AS o
    JOIN customers AS c ON o.customer_id = c.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.id, c.name
),
pareto AS (
    SELECT
        customer_name,
        CAST(total_spent AS INTEGER) AS total_spent,
        ROUND(100.0 * total_spent / SUM(total_spent) OVER (), 2) AS pct_of_revenue,
        ROUND(100.0 * SUM(total_spent) OVER (ORDER BY total_spent DESC)
            / SUM(total_spent) OVER (), 2) AS cumulative_pct,
        ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS customer_rank,
        COUNT(*) OVER () AS total_customers
    FROM customer_revenue
)
SELECT
    customer_name,
    total_spent,
    pct_of_revenue,
    cumulative_pct,
    customer_rank,
    total_customers,
    ROUND(100.0 * customer_rank / total_customers, 1) AS customer_pct
FROM pareto
WHERE cumulative_pct <= 80
ORDER BY customer_rank;

WITH customer_activity AS (
    SELECT
        c.id,
        c.grade,
        MAX(o.ordered_at) AS last_order_at
    FROM customers AS c
    LEFT JOIN orders AS o
        ON c.id = o.customer_id
       AND o.status NOT IN ('cancelled')
    WHERE c.is_active = 1
    GROUP BY c.id, c.grade
)
SELECT
    grade,
    COUNT(*) AS total_customers,
    SUM(CASE WHEN last_order_at >= '2025-01-01' THEN 1 ELSE 0 END) AS active_customers,
    SUM(CASE WHEN last_order_at < '2025-01-01' OR last_order_at IS NULL THEN 1 ELSE 0 END) AS churned_customers,
    ROUND(100.0 * SUM(CASE WHEN last_order_at < '2025-01-01' OR last_order_at IS NULL THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS churn_rate_pct
FROM customer_activity
GROUP BY grade
ORDER BY
    CASE grade WHEN 'VIP' THEN 1 WHEN 'GOLD' THEN 2 WHEN 'SILVER' THEN 3 ELSE 4 END;

WITH converted_carts AS (
    SELECT
        ca.id AS cart_id,
        ca.customer_id,
        ca.created_at AS cart_created,
        MIN(o.ordered_at) AS first_order_after
    FROM carts AS ca
    JOIN orders AS o
        ON ca.customer_id = o.customer_id
       AND o.ordered_at >= ca.created_at
       AND o.status NOT IN ('cancelled')
    WHERE ca.status = 'converted'
    GROUP BY ca.id, ca.customer_id, ca.created_at
),
hours_calc AS (
    SELECT
        cart_id,
        ROUND((julianday(first_order_after) - julianday(cart_created)) * 24, 1) AS hours_to_convert,
        CASE CAST(STRFTIME('%w', cart_created) AS INTEGER)
            WHEN 0 THEN 'Sun' WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue'
            WHEN 3 THEN 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat'
        END AS day_name,
        CAST(STRFTIME('%w', cart_created) AS INTEGER) AS dow
    FROM converted_carts
)
SELECT
    day_name,
    ROUND(AVG(hours_to_convert), 1) AS avg_hours,
    ROUND(MIN(hours_to_convert), 1) AS min_hours,
    ROUND(MAX(hours_to_convert), 1) AS max_hours,
    COUNT(*) AS converted_count
FROM hours_calc
GROUP BY day_name, dow
ORDER BY dow;

WITH step_viewers AS (
    SELECT COUNT(DISTINCT customer_id) AS cnt
    FROM product_views
    WHERE viewed_at >= '2024-01-01' AND viewed_at < '2025-01-01'
),
step_carters AS (
    SELECT COUNT(DISTINCT ca.customer_id) AS cnt
    FROM carts AS ca
    JOIN cart_items AS ci ON ca.id = ci.cart_id
    WHERE ci.added_at >= '2024-01-01' AND ci.added_at < '2025-01-01'
      AND ca.customer_id IN (
          SELECT DISTINCT customer_id
          FROM product_views
          WHERE viewed_at >= '2024-01-01' AND viewed_at < '2025-01-01'
      )
),
step_buyers AS (
    SELECT COUNT(DISTINCT o.customer_id) AS cnt
    FROM orders AS o
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled')
      AND o.customer_id IN (
          SELECT DISTINCT ca.customer_id
          FROM carts AS ca
          JOIN cart_items AS ci ON ca.id = ci.cart_id
          WHERE ci.added_at >= '2024-01-01' AND ci.added_at < '2025-01-01'
      )
),
funnel AS (
    SELECT 1 AS step, '1. View' AS funnel_step, cnt FROM step_viewers
    UNION ALL
    SELECT 2, '2. Cart', cnt FROM step_carters
    UNION ALL
    SELECT 3, '3. Order', cnt FROM step_buyers
)
SELECT
    funnel_step,
    cnt AS unique_customers,
    ROUND(100.0 * cnt / LAG(cnt) OVER (ORDER BY step), 1) AS conversion_rate_pct
FROM funnel
ORDER BY step;

WITH ranked_reviews AS (
    SELECT
        r.id,
        r.customer_id,
        r.product_id,
        r.created_at,
        ROW_NUMBER() OVER (
            PARTITION BY r.customer_id, r.product_id, DATE(r.created_at)
            ORDER BY r.created_at DESC
        ) AS rn,
        FIRST_VALUE(r.id) OVER (
            PARTITION BY r.customer_id, r.product_id, DATE(r.created_at)
            ORDER BY r.created_at DESC
        ) AS keep_id
    FROM reviews AS r
)
SELECT
    rr.id AS duplicate_review_id,
    c.name AS customer_name,
    p.name AS product_name,
    rr.created_at,
    rr.keep_id AS keep_review_id
FROM ranked_reviews AS rr
JOIN customers AS c ON rr.customer_id = c.id
JOIN products  AS p ON rr.product_id  = p.id
WHERE rr.rn > 1
ORDER BY rr.customer_id, rr.product_id, rr.created_at;

WITH supplier_sales AS (
    SELECT
        s.id AS supplier_id,
        s.company_name AS supplier,
        COUNT(DISTINCT p.id) AS product_count,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM suppliers AS s
    LEFT JOIN products AS p ON s.id = p.supplier_id
    LEFT JOIN order_items AS oi ON p.id = oi.product_id
    LEFT JOIN orders AS o ON oi.order_id = o.id
        AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY s.id, s.company_name
),
supplier_returns AS (
    SELECT
        p.supplier_id,
        COUNT(DISTINCT r.id) AS return_count,
        COUNT(DISTINCT o.id) AS order_count
    FROM products AS p
    JOIN order_items AS oi ON p.id = oi.product_id
    JOIN orders AS o ON oi.order_id = o.id
    LEFT JOIN returns AS r ON o.id = r.order_id
    GROUP BY p.supplier_id
)
SELECT
    ss.supplier,
    ss.product_count,
    CAST(ss.revenue AS INTEGER) AS revenue,
    ROUND(100.0 * ss.revenue / NULLIF(SUM(ss.revenue) OVER (), 0), 1) AS revenue_pct,
    ROUND(100.0 * COALESCE(sr.return_count, 0) / NULLIF(sr.order_count, 0), 1) AS return_rate_pct,
    CASE
        WHEN 100.0 * ss.revenue / NULLIF(SUM(ss.revenue) OVER (), 0) >= 10 THEN 'HIGH'
        WHEN 100.0 * ss.revenue / NULLIF(SUM(ss.revenue) OVER (), 0) >= 5  THEN 'MEDIUM'
        ELSE 'LOW'
    END AS risk_level
FROM supplier_sales AS ss
LEFT JOIN supplier_returns AS sr ON ss.supplier_id = sr.supplier_id
WHERE ss.revenue > 0
ORDER BY ss.revenue DESC;

WITH cohorts AS (
    SELECT
        c.id AS customer_id,
        SUBSTR(c.created_at, 1, 7) AS cohort
    FROM customers AS c
    WHERE c.created_at >= '2024-01-01'
      AND c.created_at <  '2024-07-01'
),
order_months AS (
    SELECT DISTINCT
        co.customer_id,
        co.cohort,
        CAST(
            (julianday(SUBSTR(o.ordered_at, 1, 7) || '-01')
           - julianday(co.cohort || '-01')) / 30
        AS INTEGER) AS month_offset
    FROM cohorts AS co
    JOIN orders AS o ON co.customer_id = o.customer_id
    WHERE o.status NOT IN ('cancelled')
      AND o.ordered_at >= '2024-01-01'
)
SELECT
    cohort,
    COUNT(DISTINCT customer_id) AS cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 1 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m1_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 2 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m2_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 3 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m3_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 4 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m4_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 5 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m5_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_offset = 6 THEN customer_id END)
        / COUNT(DISTINCT customer_id), 1) AS m6_pct
FROM (
    SELECT DISTINCT customer_id, cohort FROM cohorts
) AS base
LEFT JOIN order_months AS om USING (customer_id, cohort)
GROUP BY cohort
ORDER BY cohort;

WITH dec_orders AS (
    SELECT *
    FROM orders
    WHERE ordered_at >= '2024-12-01' AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
nov_revenue AS (
    SELECT SUM(total_amount) AS rev
    FROM orders
    WHERE ordered_at >= '2024-11-01' AND ordered_at < '2024-12-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
dec_sales AS (
    SELECT
        SUM(total_amount) AS revenue,
        COUNT(*) AS order_count,
        ROUND(AVG(total_amount), 0) AS avg_order_value
    FROM dec_orders
),
dec_customers AS (
    SELECT
        COUNT(DISTINCT CASE
            WHEN NOT EXISTS (
                SELECT 1 FROM orders o2
                WHERE o2.customer_id = d.customer_id
                  AND o2.ordered_at < '2024-12-01'
                  AND o2.status NOT IN ('cancelled')
            )
            THEN d.customer_id
        END) AS new_customers,
        COUNT(DISTINCT CASE
            WHEN EXISTS (
                SELECT 1 FROM orders o2
                WHERE o2.customer_id = d.customer_id
                  AND o2.ordered_at < '2024-12-01'
                  AND o2.status NOT IN ('cancelled')
            )
            THEN d.customer_id
        END) AS repeat_customers
    FROM dec_orders AS d
),
dec_shipping AS (
    SELECT ROUND(AVG(julianday(sh.delivered_at) - julianday(sh.shipped_at)), 1) AS avg_delivery_days
    FROM shipping AS sh
    JOIN orders AS o ON sh.order_id = o.id
    WHERE o.ordered_at >= '2024-12-01' AND o.ordered_at < '2025-01-01'
      AND sh.delivered_at IS NOT NULL
),
dec_returns AS (
    SELECT
        ROUND(100.0 * COUNT(DISTINCT r.id) / NULLIF(COUNT(DISTINCT o.id), 0), 1) AS return_rate_pct
    FROM orders AS o
    LEFT JOIN returns AS r ON o.id = r.order_id
    WHERE o.ordered_at >= '2024-12-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled')
),
dec_cs AS (
    SELECT COUNT(*) AS cs_tickets
    FROM complaints
    WHERE created_at >= '2024-12-01' AND created_at < '2025-01-01'
)
SELECT
    CAST(ds.revenue AS INTEGER) AS revenue,
    ROUND(100.0 * (ds.revenue - nr.rev) / nr.rev, 1) AS mom_growth_pct,
    ds.order_count,
    dc.new_customers,
    dc.repeat_customers,
    ds.avg_order_value,
    dsh.avg_delivery_days,
    dr.return_rate_pct,
    dcs.cs_tickets
FROM dec_sales AS ds
CROSS JOIN nov_revenue AS nr
CROSS JOIN dec_customers AS dc
CROSS JOIN dec_shipping AS dsh
CROSS JOIN dec_returns AS dr
CROSS JOIN dec_cs AS dcs;