Dates Hide in Business Questions
Concepts covered: Current Date, Date vs Timestamp, Date Truncation, Date Math
Business questions arrive in business words. A product manager says "monthly", "since signup", "within a week", "last quarter" or "on weekends", and the translation into date math is the candidate's job. Each of those words resolves to 1 of 5 operations: bucket a timestamp onto its calendar period, shift a date by a fixed span, measure the gap between 2 dates, pull out a repeating part such as the weekday, or bound a range with a start and an end. Naming the operation behind each word before writing SQL is most of what this question is graded on. Once the operation is chosen, the SQL for it is 1 or 2 lines, and the remaining points go to where you put the boundaries.
Translating the request
| The request says | The date math underneath | Tool |
|---|---|---|
| "Revenue by month for the past year" | Put each sale on its month, keep 12 calendar months | DATE_TRUNC plus a range |
| "Trial ends 14 days after signup" | Move signup_date forward 14 days | + INTERVAL or DATE_ADD |
| "Came back within 7 days" | Count the days from first visit to the next visit | DATE_DIFF |
| "Are Mondays our slowest day?" | Pull the weekday, pool every Monday together | EXTRACT |
| "January revenue" | Every row from Jan 1 00:00 up to, not including, Feb 1 00:00 | Half-open range |
| "Active in the last 30 days" | Shift back from today, decide whether today counts | CURRENT_DATE - INTERVAL |
Real requests stack several of these. "Weekly signups over the last quarter, split by whether the account ordered within 14 days" needs a bucket (signups per week), a range (the quarter), and a measured gap (signup to first order) evaluated per account. Breaking the request apart out loud, phrase by phrase, gives the interviewer your plan before your query. It also catches the piece a candidate who starts typing at the first noun tends to drop, which is usually the gap, because it lives on a different table from the thing being counted.
What the wording leaves open
Every relative window gets written against CURRENT_DATE (or CURRENT_TIMESTAMP when the comparison needs a time of day), never against a typed-in date. A literal such as '2024-02-14' is correct on the afternoon you type it and wrong the next morning, and a dashboard query keeps running every day after the one it was written on. In this lesson today is 2024-03-15, so CURRENT_DATE - INTERVAL '30 days' evaluates to 2024-02-14, and tomorrow it evaluates to 2024-02-15 without anyone editing it. Interviewers notice a hardcoded date because it is the line that turns a correct answer into a report that silently freezes.
How the answer gets scored
- Costs points
- Starts typing at the first noun, before settling calendar versus rolling
- Builds the month with LEFT(CAST(txn_date AS VARCHAR), 7), a string where a date belonged
- Hardcodes '2024-02-01' into a query meant to run daily
- Puts BETWEEN on a timestamp column and never checks the last day
- Earns points
- Restates each phrase as an operation: "monthly" is a bucket, "within 30 days" is a gap
- Groups with DATE_TRUNC, so the bucket stays a date and date arithmetic still works on it
- Anchors every relative window on CURRENT_DATE
- Says which end of each range is inclusive before being asked
The strongest openings fit in a single breath. For "monthly revenue by region for the past year" it sounds like: "Monthly means I bucket txn_date to the first of its month with DATE_TRUNC. Past year I'll read as the 12 complete calendar months before this one, as a range that includes its start and excludes its end. I'm assuming txn_date is a DATE; tell me if it's a timestamp." That takes around 15 seconds and settles the grain of the output and the window. If the interviewer meant a rolling 365 days, you find out before writing the query instead of after, and the same goes for the column type.
About this lesson
This section is part of the Date Arithmetic: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.