Exercises

# ============================================================
# Drill 1 — Top-N per group
# ============================================================
# TASK: 3 highest-paying orders per customer
#
# ANSWER (MySQL 8 window function):
SELECT * FROM (
  SELECT id, customer_id, total_cents,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rk
  FROM orders
) t WHERE rk <= 3;

# ============================================================
# Drill 2 — Upsert
# ============================================================
# TASK: insert or update by unique key (email)
#
# ANSWER:
INSERT INTO customers (email, name) VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

# ============================================================
# Drill 3 — Date range query
# ============================================================
# TASK: orders from today UTC
#
# ANSWER (avoid functions on indexed columns):
SELECT id FROM orders
WHERE created_at >= UTC_DATE()
  AND created_at <  UTC_DATE() + INTERVAL 1 DAY;

# DO NOT: WHERE DATE(created_at) = UTC_DATE()
# -> the function call disables the index

# ============================================================
# Drill 4 — Composite index design
# ============================================================
# TASK: most common query is
# WHERE customer_id = ? AND status = ? ORDER BY created_at DESC
#
# ANSWER:
CREATE INDEX ix_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

# Equality-Sort-Range order; this composite serves the WHERE + ORDER BY in one
# index seek.

# ============================================================
# Drill 5 — JSON column query
# ============================================================
# TASK: filter by payload->>'source' = 'web'
#
# ANSWER:
SELECT id FROM orders WHERE details->>'$.source' = 'web';

# Speed it up with a generated column + index:
ALTER TABLE orders
  ADD COLUMN source VARCHAR(32) GENERATED ALWAYS AS (details->>'$.source') STORED,
  ADD KEY ix_source (source);

# ============================================================
# Drill 6 — Bulk insert
# ============================================================
# TASK: insert 1M rows fast
#
# ANSWER:
# - Use LOAD DATA INFILE (or LOAD DATA LOCAL INFILE)
# - Or insert in batches of 1000-5000 rows
# - Wrap in a single transaction
# - SET unique_checks = 0; SET foreign_key_checks = 0;  (then restore)

LOAD DATA INFILE '/tmp/orders.csv' INTO TABLE orders
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS;

# ============================================================
# Bonus — explain
# ============================================================
# EXPLAIN ANALYZE SELECT ... (MySQL 8.0.18+)
# - 'rows' should be tiny
# - 'Extra' shouldn't say 'Using filesort' (means no index for ORDER BY)
# - 'type' should be ref / range / const / eq_ref, NOT ALL

# ============================================================
# Scoring
# 6 / 6 -> production-ready MySQL
# 4 / 6 -> bookmark mysql/cheatsheet
# < 4   -> read EXPLAIN + composite-index docs

-- Fill in: SELECT ____ FROM users;