How SQL Helps You Land a Business Analyst Job Faster

SELECT COUNT(*)                                        AS total_orders,
       SUM(CASE WHEN delivery_slot IS NULL THEN 1 ELSE 0 END) AS missing_slot,
       COUNT(DISTINCT status)                          AS status_values,
       MIN(created_at)                                 AS first_order,
       MAX(created_at)                                 AS last_order
FROM orders;

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
ORDER BY n DESC;

SELECT phone, COUNT(*) AS customers_with_same_phone
FROM customers
GROUP BY phone
HAVING COUNT(*) > 1
ORDER BY customers_with_same_phone DESC
LIMIT 20;

-- PostgreSQL: average hours spent in each stage of loan approval
SELECT stage,
       ROUND(AVG(EXTRACT(EPOCH FROM (ended_at - started_at)) / 3600), 1) AS avg_hours,
       COUNT(*) AS items
FROM application_stages
WHERE ended_at IS NOT NULL
GROUP BY stage
ORDER BY avg_hours DESC;

SELECT DATE_TRUNC('month', processed_at) AS month,
       ROUND(100.0 * SUM(CASE WHEN sent_back THEN 1 ELSE 0 END) / COUNT(*), 1) AS rework_pct
FROM document_reviews
GROUP BY 1
ORDER BY 1;

WITH steps AS (
  SELECT user_id,
         MAX(CASE WHEN event = 'view_product' THEN 1 ELSE 0 END) AS viewed,
         MAX(CASE WHEN event = 'add_to_cart'  THEN 1 ELSE 0 END) AS carted,
         MAX(CASE WHEN event = 'checkout'     THEN 1 ELSE 0 END) AS checkout,
         MAX(CASE WHEN event = 'purchase'     THEN 1 ELSE 0 END) AS purchased
  FROM events
  WHERE event_date >= DATE '2026-10-01'
  GROUP BY user_id
)
SELECT SUM(viewed) AS viewed, SUM(carted) AS carted,
       SUM(checkout) AS checkout, SUM(purchased) AS purchased
FROM steps;

-- Check a proposed mapping rule on real data before writing it down:
-- "customer_tier = GOLD if total spend in last 12 months >= 50000"
SELECT customer_id,
       SUM(amount) AS spend_12m,
       CASE WHEN SUM(amount) >= 50000 THEN 'GOLD' ELSE 'STANDARD' END AS proposed_tier
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY customer_id
ORDER BY spend_12m DESC
LIMIT 20;

-- Missing values for key fields
SELECT 'email' AS field, SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS missing FROM customers
UNION ALL
SELECT 'phone', SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) FROM customers
UNION ALL
SELECT 'city', SUM(CASE WHEN city IS NULL THEN 1 ELSE 0 END) FROM customers;

-- Weekly volume trend
SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS tickets
FROM support_tickets
GROUP BY 1
ORDER BY 1;

-- Before vs after a process change (change went live on 2026-09-01)
SELECT CASE WHEN created_at < DATE '2026-09-01' THEN 'before' ELSE 'after' END AS period,
       ROUND(AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600), 1) AS avg_hours_to_resolve,
       COUNT(*) AS tickets
FROM support_tickets
WHERE resolved_at IS NOT NULL
GROUP BY 1;