Real-World Scenario: Financial Analytics

SELECT transaction_date, description,
       CASE WHEN type = 'credit' THEN amount ELSE -amount END AS net,
       SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END)
         OVER (ORDER BY transaction_date
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_balance
FROM transactions
WHERE account_id = 1001
ORDER BY transaction_date;

Running balance = cumulative sum of credits minus debits. The CASE WHEN converts debits to negative. The SUM OVER produces the running total. Standard bank statement format.

SELECT account_id,
       DATE(transaction_date) AS txn_date,
       COUNT(*) AS failed_count
FROM transactions
WHERE status = 'failed'
GROUP BY account_id, DATE(transaction_date)
HAVING COUNT(*) >= 3
ORDER BY failed_count DESC;

Fraud detection pattern — multiple failures in a day may indicate a brute force attack or card decline. Used in risk analytics at banks and payment companies.

SELECT account_id, transaction_date,
       LAG(transaction_date) OVER (
         PARTITION BY account_id ORDER BY transaction_date
       ) AS prev_txn_date,
       DATEDIFF(transaction_date,
         LAG(transaction_date) OVER (
           PARTITION BY account_id ORDER BY transaction_date
         )
       ) AS days_since_last_txn
FROM transactions;

Transaction velocity analysis. Days between transactions reveals customer behaviour — regular vs sporadic spenders. Long gaps may indicate churned customers.

WITH account_totals AS (
  SELECT account_id,
         SUM(amount) AS total_deposits,
         NTILE(10) OVER (ORDER BY SUM(amount) DESC) AS decile
  FROM transactions
  WHERE type = 'credit'
  GROUP BY account_id
)
SELECT * FROM account_totals
WHERE decile = 1;

NTILE(10) splits accounts into 10 equal buckets (deciles). Decile 1 = top 10%. Used in customer segmentation, wealth management, and targeted marketing.

WITH daily_balance AS (
  SELECT account_id,
         DATE(transaction_date) AS txn_date,
         SUM(CASE WHEN type='credit' THEN amount ELSE -amount END)
           OVER (PARTITION BY account_id
                 ORDER BY transaction_date
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS balance
  FROM transactions
),
month_end AS (
  SELECT account_id, txn_date,
         LAST_DAY(txn_date) AS month_end,
         ROW_NUMBER() OVER (
           PARTITION BY account_id, LAST_DAY(txn_date)
           ORDER BY txn_date DESC
         ) AS rn
  FROM daily_balance
)
SELECT account_id, month_end, balance
FROM month_end WHERE rn = 1;

Month-end balance = last known balance before or on the last day of the month. This chained CTE approach builds the balance first, then picks the last record per month.