MySQL Complete Query & Formula List

Execution order se lekar window functions, CTEs, self joins aur recursive CTEs tak — har query ka syntax, real output aur common mistakes. Rolling averages, delayed rate, Top-N per category, 1:N fan-out trap. Data Analyst interview ke liye complete SQL cheat sheet.

📑 Is Article Mein Kya Hai:

• 📌 PART 0 — SQL Execution Order (yeh samajh lo, aadha SQL clear)
• 📚 PART 1 — SELECT Basics
• 🔍 PART 2 — WHERE (Filtering)
• 🧮 PART 3 — Aggregate Functions + GROUP BY
• 🎯 PART 4 — The Most Important Interview Query (Rate/Percentage)
• 🔗 PART 5 — JOINs
• 📦 PART 6 — Subqueries
• 🧩 PART 7 — CTE (Common Table Expression)
• 🪟 PART 8 — Window Functions (Analytics ka dil)
• 📅 PART 9 — Date Functions
• 🔀 PART 10 — Set Operations
• 🎨 PART 11 — CASE WHEN (SQL ka if-else)
• 🚀 PART 12 — Business Case Queries (Interview Gold)
• 🧹 PART 13 — Data Cleaning in SQL
• ⚡ PART 14 — Indexes & Performance
• 🎤 PART 15 — Interview One-Liners (MySQL)
• ✅ Revision Priority (Interview se 1 raat pehle)

Likhne ka order aur chalne ka order alag hota hai:

Likhne ka order        Chalne ka order
---------------        ---------------
SELECT                 1. FROM / JOIN
FROM                   2. WHERE
WHERE                  3. GROUP BY
GROUP BY               4. HAVING
HAVING                 5. SELECT
ORDER BY               6. DISTINCT
LIMIT                  7. ORDER BY
                       8. LIMIT

Iska practical matlab:

• WHERE me alias use nahi kar sakte (WHERE doubled > 100 ❌) — kyunki SELECT baad me chalta hai
• HAVING me alias chal jata hai MySQL me ✅ (verified)
• WHERE row-level filter hai, HAVING group-level filter hai
• Window functions WHERE ke baad chalte hain — isliye WHERE se filter karoge toh rolling average galat aayega

-- Saara data
SELECT * FROM orders;

-- Specific columns + alias ⭐
SELECT order_id, order_amount AS amount FROM orders;

-- Top N ⭐
SELECT order_id, order_amount FROM orders ORDER BY order_amount DESC LIMIT 3;

-- Distinct ⭐
SELECT DISTINCT warehouse_id FROM orders;

-- Column rename + calculation
SELECT order_id, order_amount * 1.18 AS amount_with_gst FROM orders;

-- Literal column
SELECT order_id, 'High Value' AS tag FROM orders WHERE order_amount > 15000;

Verified output (top 3):

-- Comparison ⭐
SELECT * FROM orders WHERE order_amount > 15000;
SELECT * FROM orders WHERE warehouse_id = 'W1';
SELECT * FROM orders WHERE order_amount <> 12000;      -- not equal (!= bhi chalega)

-- AND / OR / NOT ⭐
SELECT * FROM orders WHERE order_amount BETWEEN 12000 AND 20000 AND payment_mode = 'COD';
SELECT * FROM orders WHERE warehouse_id = 'W1' OR warehouse_id = 'W2';
SELECT * FROM orders WHERE NOT delivery_status = 'Delayed';

-- IN / NOT IN ⭐
SELECT * FROM orders WHERE warehouse_id IN ('W1','W2');
SELECT * FROM orders WHERE delivery_status NOT IN ('Delivered','Cancelled');

-- BETWEEN ⭐ (inclusive hai — dono end count hote hain)
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- LIKE (pattern matching) ⭐
SELECT * FROM orders WHERE customer_pincode LIKE '121%';   -- 121 se shuru
SELECT * FROM orders WHERE customer_pincode LIKE '%001';   -- 001 pe khatam
SELECT * FROM orders WHERE customer_pincode LIKE '12_001'; -- _ = ek character
SELECT * FROM orders WHERE customer_pincode NOT LIKE '1%';

-- NULL checks ⭐ (yeh sabse common galti hai)
SELECT * FROM orders WHERE actual_delivery_date IS NULL;      -- ✅
SELECT * FROM orders WHERE actual_delivery_date IS NOT NULL;  -- ✅
SELECT * FROM orders WHERE actual_delivery_date = NULL;       -- ❌ KABHI NAHI CHALEGA

Verified — WHERE customer_pincode LIKE '121%':

 order_id customer_pincode
      101           121001
      103           121001
      107           121001

-- Basic aggregates ⭐
SELECT COUNT(*)      AS total_orders,     -- saari rows (NULL bhi count)
       COUNT(actual_delivery_date) AS non_null,  -- NULL skip karta hai ⭐
       COUNT(DISTINCT warehouse_id) AS warehouses,
       SUM(order_amount)   AS total_sales,
       AVG(order_amount)   AS avg_order,
       MIN(order_amount)   AS min_order,
       MAX(order_amount)   AS max_order
FROM orders;

-- GROUP BY ⭐
SELECT warehouse_id, COUNT(*) AS orders, ROUND(SUM(order_amount),2) AS sales
FROM orders GROUP BY warehouse_id;

-- HAVING (group ke baad filter) ⭐
SELECT warehouse_id, COUNT(*) AS orders, ROUND(SUM(order_amount),2) AS sales
FROM orders GROUP BY warehouse_id HAVING COUNT(*) >= 2 ORDER BY sales DESC;

-- HAVING me alias (MySQL allow karta hai) ⭐ verified
SELECT warehouse_id, COUNT(*) AS c FROM orders GROUP BY warehouse_id HAVING c >= 3;

Verified output:

COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col) ⭐

❌ GALAT (yeh maine khud run karke prove kiya)

SELECT warehouse_id, delivery_status, COUNT(order_id) AS total_orders,
       ROUND(100.0*COUNT(CASE WHEN delivery_status='Delayed' THEN 1 END)/COUNT(order_id),2) AS delayed_rate
FROM orders GROUP BY warehouse_id, delivery_status;

Actual output — dekho kaise barbaad hua:

warehouse_id delivery_status  total_orders  delayed_rate
          W1         Delayed             2         100.0    <- har group 100% ya 0%
          W1         On-Time             1           0.0
          W2         Delayed             1         100.0
          W2         On-Time             2           0.0
          W3         On-Time             2           0.0

GROUP BY delivery_status likhne se har status ka alag group ban gaya — isliye delayed group hamesha 100% aur on-time group hamesha 0% aayega. Rate ka koi matlab hi nahi bacha.

✅ SAHI

SELECT
    warehouse_id,
    COUNT(order_id) AS total_orders,
    ROUND(
        100.0 * COUNT(CASE WHEN delivery_status = 'Delayed' THEN 1 END) / COUNT(order_id),
        2
    ) AS delayed_rate_pct
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
GROUP BY warehouse_id
HAVING delayed_rate_pct > 15.00
ORDER BY delayed_rate_pct DESC;

Verified output:

W3 filter ho gaya (0% delay). Rule: jiska rate nikalna ho, usko GROUP BY me mat daalo — usko CASE WHEN ke andar rakho.

-- INNER JOIN (sirf matching rows) ⭐
SELECT o.order_id, oi.category_name, oi.quantity
FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id;

-- LEFT JOIN (left table ki saari rows) ⭐
SELECT o.order_id, s.hub_id
FROM orders o LEFT JOIN shipments s ON o.order_id = s.order_id;

-- LEFT JOIN se "missing records" dhundna ⭐ (bahut useful)
SELECT o.order_id FROM orders o
LEFT JOIN shipments s ON o.order_id = s.order_id
WHERE s.hub_id IS NULL;                    -- shipment hi nahi hua

-- RIGHT JOIN
SELECT * FROM orders o RIGHT JOIN shipments s ON o.order_id = s.order_id;

-- FULL OUTER JOIN (MySQL me direct nahi hota — UNION se banao) [MySQL-only]
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

-- Self Join (hierarchy / comparison) ⭐
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- CROSS JOIN (cartesian)
SELECT COUNT(*) FROM orders o CROSS JOIN employees e;    -- 8 × 5 = 40 rows (verified)

-- Multi-key join
SELECT * FROM orders o JOIN shipments s
  ON o.order_id = s.order_id AND o.warehouse_id = s.warehouse_id;

Verified — self join output:

employee manager
    Amit    NULL      <- top level, koi manager nahi
   Rahul    Amit
   Priya    Amit
   Vikas   Priya
    Neha    Amit

🚨 1:N JOIN INFLATION TRAP (sabse khatarnak galti — verified proof)

Agar ek order ke 3 items hain, toh orders JOIN order_items karke SUM(order_amount) karoge toh order ka amount 3 baar jud jayega:

-- ❌ GALAT
SELECT SUM(o.order_amount) FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;
-- Result: 145200   (10 rows joined)

-- ✅ SAHI
SELECT SUM(order_amount) FROM orders;
-- Result: 121200   (8 rows)

-- ✅ SAHI (agar items bhi chahiye) — pehle aggregate karo, phir join
SELECT SUM(o.order_amount)
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS items FROM order_items GROUP BY order_id) oi
  ON o.order_id = oi.order_id;
-- Result: 121200 ✅

Actual verified numbers: 145200 (galat) vs 121200 (sahi) — ₹24,000 ka farq!

-- 1. Scalar subquery (ek value return) ⭐
SELECT order_id, order_amount FROM orders
WHERE order_amount > (SELECT AVG(order_amount) FROM orders);

-- 2. IN subquery ⭐
SELECT order_id FROM orders
WHERE order_id IN (SELECT order_id FROM order_items WHERE returned_status = 'Returned');

-- 3. EXISTS (large data pe IN se fast) ⭐
SELECT o.order_id FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items oi
              WHERE oi.order_id = o.order_id AND oi.returned_status = 'Returned');

-- 4. NOT EXISTS (jo records match NAHI karte)
SELECT o.order_id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id);

-- 5. Derived table (FROM me subquery) ⭐
SELECT warehouse_id, avg_amt FROM (
    SELECT warehouse_id, ROUND(AVG(order_amount),2) AS avg_amt
    FROM orders GROUP BY warehouse_id
) t WHERE avg_amt > 13000;

-- 6. Correlated subquery (har row ke liye alag calculation) ⭐
SELECT o.warehouse_id, o.order_id, o.order_amount FROM orders o
WHERE o.order_amount = (SELECT MAX(o2.order_amount) FROM orders o2
                        WHERE o2.warehouse_id = o.warehouse_id);

Verified — correlated subquery (top order per warehouse):

IN vs EXISTS — interview question

-- Basic CTE ⭐
WITH wh_sales AS (
    SELECT warehouse_id, SUM(order_amount) AS total
    FROM orders GROUP BY warehouse_id
)
SELECT * FROM wh_sales WHERE total > 40000 ORDER BY total DESC;

-- Multiple CTEs
WITH step1 AS (SELECT ... ),
     step2 AS (SELECT ... FROM step1)
SELECT * FROM step2;

-- Recursive CTE (hierarchy / levels) ⭐
WITH RECURSIVE hier AS (
    SELECT emp_id, emp_name, manager_id, 1 AS lvl
    FROM employees WHERE manager_id IS NULL          -- anchor
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.manager_id, h.lvl + 1
    FROM employees e JOIN hier h ON e.manager_id = h.emp_id   -- recursive part
)
SELECT * FROM hier ORDER BY lvl, emp_id;

Verified — recursive CTE output:

CTE vs Subquery — kaun better?

• Readability: CTE jeetta hai — top-down padh sakte ho
• Reuse: Ek CTE ko multiple baar reference kar sakte ho, subquery ko copy-paste karna padta
• Recursion: Sirf CTE se possible
• Performance: MySQL 8+ me dono kaafi similar; CTE materialize ho sakta hai

8.1 Ranking Functions ⭐

SELECT category_name, product_id, cnt,
       ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY cnt DESC) AS rn,
       RANK()       OVER (PARTITION BY category_name ORDER BY cnt DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY category_name ORDER BY cnt DESC) AS drk
FROM (SELECT category_name, product_id, COUNT(*) AS cnt
      FROM order_items GROUP BY category_name, product_id) t;

8.2 Top N per Group (SUPER COMMON interview question) ⭐

WITH category_returns AS (
    SELECT category_name, product_id, COUNT(order_id) AS total_returns,
           DENSE_RANK() OVER (PARTITION BY category_name ORDER BY COUNT(order_id) DESC) AS rank_num
    FROM order_items
    WHERE returned_status = 'Returned'
    GROUP BY category_name, product_id
)
SELECT category_name, product_id, total_returns, rank_num
FROM category_returns
WHERE rank_num <= 2
ORDER BY category_name, rank_num;

Verified output:

8.3 Rolling / Moving Average ⭐

SELECT warehouse_id, dispatch_date, dispatched_units,
       ROUND(AVG(dispatched_units) OVER (
             PARTITION BY warehouse_id
             ORDER BY dispatch_date
             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW      -- ⭐ yeh line MUST hai
       ), 2) AS rolling_7d_avg
FROM daily_warehouse_dispatches
ORDER BY warehouse_id, dispatch_date;

Verified output (W1):

❌ Bug #1 — Frame clause bhool gaye

AVG(dispatched_units) OVER (PARTITION BY warehouse_id ORDER BY dispatch_date)

Verified: yeh cumulative running average deta hai (day 1 se aaj tak), rolling nahi.

❌ Bug #2 — WHERE se pehle filter kar diya

WHERE dispatch_date > (SELECT MAX(dispatch_date) FROM daily_warehouse_dispatches) - INTERVAL 7 DAY

Verified output — dekho rolling average galat ho gaya:

dispatch_date  dispatched_units  wrong_rolling
   2024-01-08               109         109.00   <- pehla din hi 109 (sirf khud ka avg)
   2024-01-09               116         112.50
   2024-01-14               111         118.57

Sahi answer 116.57 hona chahiye tha. Window function WHERE ke BAAD chalta hai — isliye filter karne se purane din gayab ho jaate hain.

ROWS vs RANGE — verified difference

AVG(u) OVER (PARTITION BY w ORDER BY d ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW)
AVG(u) OVER (PARTITION BY w ORDER BY d RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW)

Data me date gap hai (Jan 2 ke baad Jan 5):

     d      u  rows_frame  range_frame
2024-01-01 100      100.0       100.0
2024-01-02 110      105.0       105.0
2024-01-05 120      110.0       120.0   <- ROWS ne Jan 2 ko le liya, RANGE ne nahi
2024-01-06 130      120.0       125.0

• ROWS = physical row count. Date missing ho toh galat window.
• RANGE = value-based. Date gaps handle karta hai. ✅ Time series ke liye better.

8.4 LAG / LEAD ⭐

SELECT card_number, transaction_time,
       LAG(transaction_time, 1) OVER (PARTITION BY card_number ORDER BY transaction_time) AS prev_time,
       LEAD(transaction_time, 1) OVER (PARTITION BY card_number ORDER BY transaction_time) AS next_time,
       transaction_amount - LAG(transaction_amount, 1) OVER (PARTITION BY card_number ORDER BY transaction_time) AS diff
FROM transactions;

8.5 Running Total & Partition Total ⭐

SELECT order_id, warehouse_id, order_amount,
       SUM(order_amount) OVER (ORDER BY order_id)                       AS running_total,
       SUM(order_amount) OVER (PARTITION BY warehouse_id)               AS wh_total,
       ROUND(100.0 * order_amount / SUM(order_amount) OVER (PARTITION BY warehouse_id), 2) AS pct_of_wh
FROM orders ORDER BY order_id;

Verified output:

8.6 NTILE, FIRST_VALUE, NTH_VALUE

SELECT order_id, order_amount, NTILE(4) OVER (ORDER BY order_amount) AS quartile FROM orders;

SELECT order_id, warehouse_id,
       FIRST_VALUE(order_id) OVER (PARTITION BY warehouse_id ORDER BY order_amount DESC) AS top_order_in_wh
FROM orders;

Verified NTILE:

-- Extract ⭐
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date),
       QUARTER(order_date), WEEK(order_date), DAYNAME(order_date), MONTHNAME(order_date);

-- Format [MySQL-only]
SELECT DATE_FORMAT(order_date, '%b-%Y');     -- "Jan-2024"
SELECT DATE_FORMAT(order_date, '%Y-%m');     -- "2024-01"
SELECT DATE_FORMAT(order_date, '%d-%m-%Y');  -- "05-01-2024"

-- Difference ⭐ [MySQL-only]
SELECT DATEDIFF(actual_delivery_date, order_date) AS tat_days FROM orders;
SELECT TIMESTAMPDIFF(MINUTE, dispatch_time, delivered_time) AS delivery_minutes FROM shipments;
SELECT TIMESTAMPDIFF(DAY, hire_date, CURDATE()) AS tenure_days FROM employees;

-- Add / Subtract [MySQL-only] ⭐
SELECT DATE_ADD(order_date, INTERVAL 7 DAY);
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH);
SELECT order_date + INTERVAL 7 DAY;                    -- yeh bhi chalega

-- Current ⭐
SELECT CURDATE(), NOW(), CURRENT_TIMESTAMP, UNIX_TIMESTAMP();

-- Month-wise grouping ⭐
SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month, COUNT(*) AS orders
FROM orders GROUP BY order_month ORDER BY order_month;

Verified — month-wise sales:

order_month  orders   sales
    2024-01       2 32000.0
    2024-02       2 22000.0
    2024-03       2 24300.0
    2024-04       2 42900.0

🔥 SARGABLE vs NON-SARGABLE (yeh bolna = instant seniority)

-- ❌ NON-SARGABLE — index use nahi hoga, full table scan
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
SELECT * FROM orders WHERE DATE_FORMAT(order_date,'%Y-%m') = '2024-01';
SELECT * FROM orders WHERE LEFT(customer_pincode, 3) = '121';

-- ✅ SARGABLE — index lagega
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
SELECT * FROM orders WHERE customer_pincode LIKE '121%';

Verified: dono queries same 8 rows deti hain — farq sirf performance ka hai. Badi table pe non-sargable query 100x slow ho sakti hai.

SELECT order_id FROM orders WHERE warehouse_id = 'W1'
UNION ALL                                    -- duplicates rakhta hai (fast)
SELECT order_id FROM orders WHERE delivery_status = 'Delayed';

SELECT order_id FROM orders WHERE warehouse_id = 'W1'
UNION                                        -- duplicates hatata hai (slow — sort karta hai)
SELECT order_id FROM orders WHERE delivery_status = 'Delayed';

-- EXCEPT / MINUS (jo pehle me hai, dusre me nahi)
SELECT DISTINCT customer_id FROM orders WHERE order_date < '2024-02-01'
EXCEPT
SELECT DISTINCT customer_id FROM orders WHERE order_date >= '2024-03-01';

-- INTERSECT (dono me common)
SELECT customer_id FROM orders WHERE warehouse_id='W1'
INTERSECT
SELECT customer_id FROM orders WHERE payment_mode='Prepaid';

Verified — UNION vs UNION ALL:

             k   c
union_all_rows  6     <- 3 (W1) + 3 (Delayed) = 6, duplicate 102 do baar
    union_rows  4     <- duplicate hat gaya

Verified — EXCEPT (Jan me khareeda, March me nahi): customer_id = 2

-- Bucketing ⭐
SELECT CASE WHEN order_amount >= 20000 THEN 'A - High'
            WHEN order_amount >= 12000 THEN 'B - Medium'
            ELSE 'C - Low' END AS grade,
       COUNT(*) AS orders, ROUND(SUM(order_amount),2) AS sales
FROM orders GROUP BY grade ORDER BY grade;

-- Conditional aggregation (PIVOT ka tarika) ⭐
SELECT warehouse_id,
       SUM(CASE WHEN delivery_status = 'On-Time' THEN 1 ELSE 0 END) AS on_time,
       SUM(CASE WHEN delivery_status = 'Delayed' THEN 1 ELSE 0 END) AS delayed
FROM orders GROUP BY warehouse_id;

-- NULL ko handle karna (COALESCE / IFNULL) ⭐
SELECT order_id, COALESCE(actual_delivery_date, '1900-01-01') FROM orders;
SELECT order_id, IFNULL(discount, 0) FROM orders;                 -- MySQL

-- Simple CASE
SELECT order_id, CASE payment_mode WHEN 'COD' THEN 'Cash' ELSE 'Digital' END FROM orders;

Verified — CASE bucketing:

     grade  orders   sales
  A - High       2 45500.0
B - Medium       4 54900.0
   C - Low       2 20800.0

Verified — conditional aggregation (pivot):

12.1 OTD % (On-Time Delivery)

SELECT warehouse_id, COUNT(*) AS shipments,
       ROUND(100.0 * SUM(CASE WHEN delivery_status = 'On-Time' THEN 1 ELSE 0 END) / COUNT(*), 2) AS otd_pct
FROM shipments GROUP BY warehouse_id WITH ROLLUP;          -- [MySQL-only] ROLLUP = grand total row

12.2 Hub-wise SLA Breach (Flipkart case) ⭐

SELECT s.hub_id,
       COUNT(*) AS shipments,
       ROUND(100.0 * SUM(CASE WHEN o.actual_delivery_date > o.promised_delivery_date
                              THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_breach_pct
FROM shipments s
JOIN orders o ON s.order_id = o.order_id
WHERE o.order_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY s.hub_id
HAVING COUNT(*) >= 500                       -- chhote hubs ko filter karo
ORDER BY sla_breach_pct DESC
LIMIT 5;

Verified (HAVING >= 2 ke saath demo):

12.3 Return Rate — COD vs Prepaid ⭐

SELECT o.payment_mode, COUNT(*) AS items,
       ROUND(100.0 * SUM(CASE WHEN oi.returned_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS return_pct
FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.payment_mode ORDER BY return_pct DESC;

Verified:

👉 Insight: COD pe return rate 2x hai. Yeh Flipkart RTO case ka core finding hai.

12.4 Month-over-Month Growth (LAG se) ⭐

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS mo, SUM(order_amount) AS sales
    FROM orders GROUP BY mo
)
SELECT mo, ROUND(sales,2) AS sales,
       ROUND(LAG(sales) OVER (ORDER BY mo), 2) AS prev_sales,
       ROUND(100.0 * (sales - LAG(sales) OVER (ORDER BY mo)) / LAG(sales) OVER (ORDER BY mo), 2) AS mom_pct
FROM monthly ORDER BY mo;

Verified:

12.5 ABC Analysis (Pareto 80/15/5) ⭐

WITH sku_sales AS (
    SELECT category_name, SUM(quantity * unit_price) AS rev
    FROM order_items GROUP BY category_name
),
ranked AS (
    SELECT category_name, rev,
           SUM(rev) OVER (ORDER BY rev DESC) AS cum_rev,
           SUM(rev) OVER ()                  AS total_rev
    FROM sku_sales
)
SELECT category_name, ROUND(rev,2) AS revenue,
       ROUND(100.0 * cum_rev / total_rev, 2) AS cumulative_pct,
       CASE WHEN 100.0 * cum_rev / total_rev <= 80 THEN 'A'
            WHEN 100.0 * cum_rev / total_rev <= 95 THEN 'B'
            ELSE 'C' END AS abc_class
FROM ranked ORDER BY revenue DESC;

Verified:

12.6 Fraud Velocity (Mastercard case) ⭐

WITH transaction_velocity AS (
    SELECT card_number, transaction_id, transaction_amount, transaction_time,
           LAG(transaction_time, 1) OVER (PARTITION BY card_number ORDER BY transaction_time) AS prev_time,
           AVG(transaction_amount)  OVER (PARTITION BY card_number)                            AS avg_card_spend
    FROM transactions
)
SELECT card_number, transaction_id, transaction_amount,
       ROUND(avg_card_spend, 2) AS avg_spend,
       TIMESTAMPDIFF(MINUTE, prev_time, transaction_time) AS mins_gap,     -- [MySQL-only]
       'High Risk - Velocity & Amount Spike' AS risk_flag
FROM transaction_velocity
WHERE TIMESTAMPDIFF(MINUTE, prev_time, transaction_time) <= 10
  AND transaction_amount > (3 * avg_card_spend);

12.7 Repeat Customers / Cohort

-- Purchase frequency
SELECT purchase_count, COUNT(*) AS customers FROM (
    SELECT customer_id, COUNT(*) AS purchase_count FROM orders GROUP BY customer_id
) t GROUP BY purchase_count ORDER BY purchase_count DESC;

-- First purchase month (cohort base)
SELECT customer_id, MIN(order_date) AS first_purchase FROM orders GROUP BY customer_id;

-- AOV & Revenue per customer
SELECT warehouse_id, COUNT(*) AS orders,
       ROUND(AVG(order_amount), 2)                              AS avg_order_value,
       ROUND(SUM(order_amount) / COUNT(DISTINCT customer_id),2) AS revenue_per_customer
FROM orders GROUP BY warehouse_id;

Verified:

12.8 Running Total per Customer

SELECT customer_id, order_id, order_amount,
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_id) AS cust_running_total
FROM orders WHERE customer_id = 1;

Verified:

12.9 Off-Hour Anomaly

SELECT card_number, transaction_id, transaction_amount, HOUR(transaction_time) AS txn_hour
FROM transactions WHERE HOUR(transaction_time) BETWEEN 2 AND 4;

-- Duplicates dhundna ⭐
SELECT customer_id, order_date, COUNT(*) AS cnt
FROM orders GROUP BY customer_id, order_date HAVING COUNT(*) > 1;

-- Duplicates delete karna (lowest id rakho) [MySQL-only] ⭐
DELETE o1 FROM orders o1
INNER JOIN orders o2
  ON o1.order_id = o2.order_id AND o1.row_id > o2.row_id;

-- Window function wala tarika
DELETE FROM orders WHERE row_id IN (
    SELECT row_id FROM (
        SELECT row_id, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY row_id) AS rn
        FROM orders
    ) t WHERE rn > 1
);

-- NULL ko replace ⭐
SELECT COALESCE(actual_delivery_date, order_date) FROM orders;
UPDATE orders SET delivery_status = 'Unknown' WHERE delivery_status IS NULL;

-- Text clean ⭐
SELECT TRIM(customer_pincode),
       UPPER(delivery_status),
       LOWER(customer_pincode),
       REPLACE(customer_pincode, '-', ''),
       LEFT(customer_pincode, 3),
       SUBSTRING(customer_pincode, 1, 3),
       LENGTH(customer_pincode)
FROM orders;

-- Type cast ⭐
SELECT CAST(order_amount AS SIGNED),
       CAST('2024-01-05' AS DATE),
       CONVERT(order_amount, DECIMAL(10,2));

-- String to date
SELECT STR_TO_DATE('05-01-2024', '%d-%m-%Y');          -- [MySQL-only]

Verified — dedup: 9 rows → 8 rows (duplicate order 101 hat gaya)

-- Index banana ⭐
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_wh_date ON orders(warehouse_id, order_date);   -- composite
CREATE UNIQUE INDEX idx_order_id ON orders(order_id);

-- Index dekhna / hatana
SHOW INDEX FROM orders;
DROP INDEX idx_orders_date ON orders;

-- Query plan dekhna ⭐ (interview me poochte hain)
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-01-01';
EXPLAIN ANALYZE SELECT ...;      -- MySQL 8.0.18+

Composite Index ka rule — Leftmost Prefix ⭐

Index (warehouse_id, order_date) pe:

Performance tips (interview answers)

• SELECT * mat likho — sirf zaroori columns lo (less I/O)
• WHERE me column pe function mat lagao (non-sargable)
• JOIN pe indexed column use karo
• DISTINCT / UNION sort karte hain — zaroorat ho tabhi
• LIMIT ke saath ORDER BY indexed column pe ho
• Badi table pe EXISTS > IN
• WHERE pe LIKE '%abc' (leading wildcard) index tod deta hai

Tier 1 — MUST (yeh 20 aana hi chahiye): SELECT/WHERE/ORDER BY/LIMIT · GROUP BY + HAVING · COUNT/SUM/AVG · CASE WHEN rate calculation · LEFT/INNER JOIN · IS NULL · IN / LIKE / BETWEEN · DATE functions · COALESCE · DISTINCT · UNION ALL

Tier 2 — Strong impression: CTE · ROW_NUMBER/RANK/DENSE_RANK · LAG/LEAD · SUM() OVER() · ROWS BETWEEN frame · EXISTS · Self join · Correlated subquery

Tier 3 — Differentiator: Recursive CTE · NTILE · RANGE vs ROWS · EXPLAIN + Index strategy · Sargability · Fan-out trap · GROUP_CONCAT · WITH ROLLUP

📝 Run karke practice karo

_verify/mysql_run_all.py       # saari queries + real output
_verify/mysql_run_part2.py     # advanced + trap proofs
_verify/mysql_testbed.py       # sample tables (orders, shipments, transactions...)

pip install duckdb
python3 _verify/mysql_run_all.py

DuckDB MySQL jaisa SQL dialect hai — CTE, window functions, joins sab same chalte hain. Queries khud chalao, output dekho, values badlo. Tabhi pakka yaad rahega.

MySQL Query Cheat Sheet — Complete!

Jo queries DuckDB SQL engine pe actually execute karke verify ki gayi hain, unke real output diye gaye hain. Jo functions MySQL-specific hain unhe [MySQL-only] tag se honestly mark kiya gaya hai.

Happy Learning & Keep Exploring! 🚀