SQL Interview Deep Dive
Date and time operations account for 3.5% of SQL interview questions. They rarely appear as standalone problems, but nearly every pipeline and reporting query involves at least one date calculation.
Practice retention cohorts, gap analysis, and time-series queries with real SQL execution.
Every function below shows up in data engineering interviews. Know the syntax, know the dialect differences, and know when to reach for each one.
DATE_TRUNC('month', event_timestamp)Truncates a timestamp to the specified precision. This is how you group events by week, month, or quarter. Most retention and cohort queries start here.
Interview tip: When a question says 'monthly active users,' your first instinct should be DATE_TRUNC. It is the standard way to bucket timestamps into periods.
DATE_DIFF('day', start_date, end_date)Returns the difference between two dates in the specified unit. Syntax varies by dialect: Postgres uses age() or subtraction, BigQuery uses DATE_DIFF, Snowflake uses DATEDIFF.
Interview tip: Know your dialect. Postgres subtracts dates directly (end_date - start_date). BigQuery and Snowflake have dedicated DATE_DIFF functions with different argument orders.
EXTRACT(dow FROM event_timestamp)Pulls a specific component out of a date or timestamp: year, month, day, hour, day of week. Useful for slicing data by time dimensions without truncating.
Interview tip: EXTRACT(dow) returns 0 for Sunday in Postgres but 1 for Sunday in other dialects. This is a common source of off-by-one bugs in interview solutions.
event_timestamp + INTERVAL '30 days'Add or subtract fixed time periods from dates. Intervals let you define retention windows, grace periods, and lookback ranges without hardcoding dates.
Interview tip: Intervals and DATE_ADD serve the same purpose. Postgres uses INTERVAL syntax. BigQuery uses DATE_ADD(date, INTERVAL n DAY). Know both forms.
event_timestamp AT TIME ZONE 'America/New_York'Converts timestamps between time zones. Critical when your data stores UTC but the business question asks about local time (e.g., 'orders placed before 9am ET').
Interview tip: If a question mentions 'business hours' or 'local time,' you need to convert from UTC. Forgetting this is a silent bug that produces wrong results.
A GROUP BY question has one right answer. A date question has multiple plausible answers that differ based on how you handle edge cases. Interviewers use this ambiguity to test your attention to detail.
Consider "calculate monthly active users." Do you truncate to calendar months or rolling 30-day windows? Do you count a user active on the boundary date in one month or both? What happens to users in UTC+12 whose midnight falls in a different calendar day than your server's timezone? Each choice changes the result.
Date functions also vary more across dialects than almost any other SQL feature. DATE_TRUNC argument order, DATEDIFF vs. subtraction, INTERVAL syntax, and timezone conversion functions are all different in Postgres, BigQuery, Snowflake, and MySQL. You need to be fluent in at least one dialect and aware of the differences in others.
Finally, date questions compound with other skills. Retention analysis requires date functions plus window functions plus CTEs. Gap analysis requires LAG plus DATE_DIFF. These multi-step problems are where most candidates lose time.
Five problems at interview difficulty. Each one tests a different date function pattern.
Given a users table (user_id, signup_date) and an events table (user_id, event_date), calculate the percentage of users who were active on exactly day 1, day 7, day 14, and day 28 after signup. Group by signup month.
Hint: DATE_TRUNC the signup_date to month for cohort grouping. Use DATE_DIFF between event_date and signup_date to bucket activity into day 1, 7, 14, 28.
Given a logins table (user_id, login_date), find all users who had a gap of 7 or more days between consecutive logins. Return user_id, gap_start, gap_end, and gap_length_days.
Hint: Use LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) to get the previous login, then compute the difference.
Given an events table (user_id, event_timestamp), calculate the average weekly active users (WAU) for each quarter in 2024. A user counts as active in a week if they had at least one event that week.
Hint: DATE_TRUNC to week for WAU counting, then DATE_TRUNC to quarter for the final grouping. COUNT(DISTINCT user_id) per week, then AVG per quarter.
Given a support_tickets table (ticket_id, created_at in UTC, region), calculate the average resolution time in business hours only (9am to 6pm local time, Mon to Fri). The region column maps to a timezone.
Hint: Convert created_at to local time using AT TIME ZONE. Use EXTRACT(dow) and EXTRACT(hour) to filter to business hours. This is a multi-step problem.
Given an orders table (order_id, order_date, amount), calculate month-over-month revenue growth percentage. Exclude the current (incomplete) month. Handle months with zero revenue.
Hint: DATE_TRUNC to month, then use LAG to get previous month revenue. Filter out the current month with DATE_TRUNC(order_date) < DATE_TRUNC(CURRENT_DATE). Use COALESCE for zero-revenue months.
Given a users table (user_id, signup_date) and an events table (user_id, event_date), calculate the percentage of users in each monthly signup cohort who were active exactly 7 days after signup.
SELECT
DATE_TRUNC('month', u.signup_date) AS cohort_month,
COUNT(DISTINCT u.user_id) AS cohort_size,
COUNT(DISTINCT e.user_id) AS retained_day7,
ROUND(
100.0 * COUNT(DISTINCT e.user_id)
/ COUNT(DISTINCT u.user_id), 1
) AS retention_pct
FROM users u
LEFT JOIN events e
ON e.user_id = u.user_id
AND e.event_date = u.signup_date + INTERVAL '7 days'
GROUP BY DATE_TRUNC('month', u.signup_date)
ORDER BY cohort_month;DATE_TRUNC buckets each user into their signup month. The LEFT JOIN condition pins the match to exactly 7 days after signup, so only users who had an event on that specific day count as retained. COUNT(DISTINCT e.user_id) ignores NULLs from the LEFT JOIN, so non-retained users contribute to cohort_size but not to retained_day7.
Expected output
cohort_month | cohort_size | retained_day7 | retention_pct
--------------+-------------+---------------+--------------
2024-01-01 | 320 | 134 | 41.9
2024-02-01 | 285 | 108 | 37.9
2024-03-01 | 410 | 189 | 46.1Reading about DATE_TRUNC is not the same as writing a retention query under time pressure. Practice with real SQL execution.