Null, Timezone, and Data Type Pitfalls
These edge cases are common interview traps because they silently corrupt KPIs.
Hard Prompt #
Explain how daily revenue can differ by timezone boundaries and write SQL with explicit null-safe filters and deterministic day logic.
Two caveats this demo can't escape. First, order_date is date-only (no time component), so every row shifts a full day back. A real timezone boundary split only moves orders placed near midnight, so treat this as illustrating the offset mechanic, not a true partial-day re-bucketing. Second, the fixed - INTERVAL '5 hours' shift labeled est_day is only correct half the year — US Eastern is UTC-5 in winter (EST) but UTC-4 in summer (EDT), so a constant offset mis-buckets evening orders for months at a time. Real conversion uses an IANA zone name, e.g. order_ts_utc AT TIME ZONE 'America/New_York', so the engine applies the right offset per timestamp. Saying exactly that in an interview ("fixed offset is a demo simplification; production needs a real timestamp and IANA zones") is what scores the point.
Validation Query #
-- Check data quality for null statuses and negative order totals
SELECT
SUM(CASE WHEN order_status IS NULL THEN 1 ELSE 0 END) AS null_status_rows,
SUM(CASE WHEN order_total < 0 THEN 1 ELSE 0 END) AS negative_total_rows
FROM ecom_orders;Scoring Rubric #
- identifies null/type/timezone risks clearly (40%)
- proposes deterministic handling rules (30%)
- includes quality checks (20%)
- communicates assumptions (10%)
If time boundaries are implicit, day-level metrics are unreliable.