📋Cheat sheet & checklist

Contest-day checklist

SQL in one screen

-- order: FROM → WHERE → GROUP BY → HAVING → SELECT/WINDOW → DISTINCT → ORDER BY → LIMIT
-- top-N per group / dedup latest
WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY k ORDER BY t DESC, id) rn FROM tbl)
SELECT * FROM r WHERE rn = 1;
-- running total / moving avg / % of total
SUM(x) OVER (PARTITION BY k ORDER BY t ROWS UNBOUNDED PRECEDING)
AVG(x) OVER (ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
100.0 * x / SUM(x) OVER ()
-- previous row
LAG(x) OVER (PARTITION BY k ORDER BY t)
-- islands: d - ROW_NUMBER() OVER (PARTITION BY u ORDER BY d) = group key
-- sessions: flag new group with CASE on LAG, then SUM(flag) OVER (PARTITION BY u ORDER BY t)
-- pivot: SUM(CASE WHEN s = 'x' THEN 1 ELSE 0 END) AS x
-- anti-join: LEFT JOIN … WHERE r.id IS NULL   |   NOT EXISTS (never NOT IN with NULLs)
-- safe math: 100.0 * a / NULLIF(b, 0),  COALESCE(SUM(x), 0)
-- MySQL: DATE_FORMAT(d,'%Y-%m') DATEDIFF(a,b) TIMESTAMPDIFF(MINUTE,a,b) d + INTERVAL 1 DAY
-- SQLite: strftime('%Y-%m',d) julianday(a)-julianday(b) date(d,'+1 day')

Python in one screen

import sys, re, json, csv, io, heapq, bisect
from collections import Counter, defaultdict, deque
from itertools import groupby, accumulate, pairwise
from datetime import datetime, timedelta
from decimal import Decimal, ROUND_HALF_UP

lines = sys.stdin.read().splitlines()           # bulk read
sys.stdout.write("\n".join(out) + "\n")         # bulk write
rows.sort(key=lambda r: (-r[1], r[0]))          # desc then asc
Counter(x).most_common()                        # then sort for deterministic ties!
(t2 - t1).total_seconds()                       # not .seconds
Decimal(s).quantize(Decimal("0.01"), ROUND_HALF_UP)   # money rounding
csv.DictReader(io.StringIO(text))               # never split(',') on quoted CSV
d.get("a", {}).get("b")                         # safe nested JSON
# pandas: groupby(...).agg(n=("c","size")), .transform, .shift, .rank(method="dense"),
#         drop_duplicates(keep="last"), merge(validate="m:1"), reset_index(drop=True)

Edge cases to test before every submit

Category	Ask yourself
Empty	0 rows? An empty line at the end? A group with no members?
Duplicates	Duplicate keys, identical rows, duplicate timestamps (ties)?
NULL / missing	Missing JSON fields, empty strings, "null", NaN?
Ordering	Tie-breakers specified? Numeric vs string sort?
Boundaries	Is a gap of exactly 30 min a new session? Inclusive or exclusive date ranges?
Time	Timezones, midnight crossings, month ends, leap years, unsorted input?
Numbers	Integer division, rounding mode, negative values, overflow (not in Python), float equality?
Scale	What if n = 10⁶? Any O(n²) loops or in list lookups?

If you get an interview: talking points

• Why DataChef: data mesh and platform work on AWS, a senior consulting culture, a remote team, and Iranian engineers already there.
• Have one project story ready in STAR format: the problem, your design choices (batch vs stream, file format, partitioning, idempotency), the trade-offs, and measurable results.
• Be ready to whiteboard: "Design a pipeline that ingests orders from Postgres into a lake and serves daily revenue." One answer: CDC (Debezium) → Kafka/MSK → S3 bronze (Parquet/Iceberg, partitioned by dt) → Spark/Glue dedup + MERGE → silver → dbt marts → Athena/Redshift. Add data-quality tests, Airflow orchestration and monitoring, all idempotent and backfillable.
• Expect a take-home afterwards: clean code, tests, a README, a Makefile/Docker setup, type hints, and clear trade-offs.

MySQL vs SQLite in one screen

-- concat:      CONCAT(a, b)                 | a || b            (|| is OR in MySQL!)
-- month:       DATE_FORMAT(d, '%Y-%m')      | strftime('%Y-%m', d)
-- day diff:    DATEDIFF(b, a)               | julianday(b) - julianday(a)
-- minute diff: TIMESTAMPDIFF(MINUTE, a, b)  | (julianday(b) - julianday(a)) * 1440
-- shift:       d + INTERVAL 1 DAY           | date(d, '+1 day')
-- int divide:  a DIV b   (a / b is decimal) | a / b   (integer if both are ints)
-- strings:     case-insensitive '=' by default | case-sensitive
-- quoting:     `identifier`, 'string'       | "identifier", 'string'

Rapid-fire interview answers

Question	Short answer
ETL or ELT?	ELT on a modern warehouse/lakehouse: land raw data, transform with SQL/dbt, keep raw for replays. ETL when data must be cleaned or masked before it lands (PII).
How do you make a job idempotent?	Deterministic input (logical date), overwrite the partition or MERGE on a key, write to a temp location and swap atomically.
Parquet vs Avro?	Parquet: columnar, for analytics. Avro: row-based with an embedded schema, for messages and write-heavy pipelines.
A Spark job is slow. Where do you look?	Spark UI: skewed tasks, shuffle size, spills, number and size of partitions and files, broadcast opportunities, Python UDFs.
How do you handle late data?	Event time plus a watermark; reprocess affected partitions in batch; define the policy (drop, update, side output).
Exactly-once?	At-least-once delivery plus an idempotent sink (dedupe on event id / MERGE), or transactional writes with checkpoints.
How do you test data pipelines?	Unit tests on transformations with small fixtures, schema/contract tests, data-quality checks in the pipeline (nulls, uniqueness, row counts, freshness), and alerts.
SCD type 1 vs 2?	Type 1 overwrites (no history). Type 2 adds a row with valid_from/valid_to/is_current so facts can join to the version valid at that time.
Partitioning vs bucketing?	Partitioning: folders by a low-cardinality filter column (date) for pruning. Bucketing/clustering: hash a high-cardinality key into N files to speed up joins.
What is a data mesh?	Domain-owned data products on a self-serve platform with federated governance. Organisational as much as technical.