Real Business SQL Scenarios

What You'll Learn

Expert SQL in industry means writing queries for business problems across payments, banking, fraud detection, e-commerce, and marketing. These require combining multiple concepts: window functions, CTEs, CASE WHEN, and date math in single complex queries.

Syntax

-- No single syntax — combines all advanced patterns
-- CTEs + Window Functions + Aggregates + CASE WHEN

Example

-- Payments: daily GMV and failure rate
SELECT
  DATE(txn_time) AS date,
  COUNT(*) AS total_txns,
  SUM(amount) AS gmv,
  ROUND(SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END)*100.0
    / COUNT(*), 2) AS failure_rate_pct
FROM transactions GROUP BY DATE(txn_time);

-- Fraud: accounts with 5+ txns in 1 hour
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
WHERE txn_time >= NOW() - INTERVAL 1 HOUR
GROUP BY account_id
HAVING COUNT(*) >= 5;

-- E-commerce: Customer Lifetime Value
SELECT
  customer_id,
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(total) AS lifetime_value,
  AVG(total) AS avg_order_value,
  DATEDIFF(MAX(order_date), MIN(order_date)) AS days_active
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

-- Marketing: Revenue by channel
SELECT
  utm_source, utm_medium,
  COUNT(DISTINCT user_id) AS users,
  SUM(revenue) AS total_revenue,
  ROUND(SUM(revenue)/COUNT(DISTINCT user_id),2) AS rev_per_user
FROM orders JOIN sessions USING (session_id)
GROUP BY utm_source, utm_medium
ORDER BY total_revenue DESC;

Common Mistakes

Interview Tips

Practice