SQL Date Functions Practice for Interviews
Date and time operations account for 3.5% of SQL interview questions. They rarely appear standalone, but nearly every pipeline and reporting query involves at least one date calculation.
What this guide covers
Date and time operations account for ~3.5% of SQL interview questions standalone, but nearly every pipeline and reporting query involves at least one date calculation. Five function families cover the corpus: DATE_TRUNC, DATE_DIFF, EXTRACT, interval arithmetic, time zone handling. Each has dialect differences that trip up candidates.
Date functions you need to know
Every function below shows up in DE interviews. Know the syntax, know the dialect differences, and know when to reach for each one.
DATE_TRUNC
Syntax: DATE_TRUNC('month', event_timestamp). Truncates a timestamp to the specified precision. How you group events by week, month, or quarter. Most retention and cohort queries start here. Use cases: cohort signups by month; aggregate daily revenue into weekly buckets; align timestamps to calendar boundaries; build time-series charts from raw event data. Interview tip: when a question says 'monthly active users', your first instinct should be DATE_TRUNC. The standard way to bucket timestamps into periods.
DATE_DIFF / DATEDIFF
Syntax: DATE_DIFF('day', start_date, end_date). Difference between two dates in the specified unit. Syntax varies: Postgres uses subtraction or age(), BigQuery uses DATE_DIFF, Snowflake uses DATEDIFF. Use cases: user retention (days since signup); time between events (order to delivery); gaps in activity longer than N days; average session duration. Interview tip: know your dialect. Postgres subtracts dates directly (end_date - start_date). BigQuery and Snowflake have dedicated functions with different argument orders.
EXTRACT
Syntax: EXTRACT(dow FROM event_timestamp). Pulls a specific component out of a date or timestamp: year, month, day, hour, day of week. For slicing data by time dimensions without truncating. Use cases: which day of week has the most orders; filter to business hours (EXTRACT hour); compare Q1 vs Q2 (EXTRACT quarter); identify seasonal patterns by month number. Interview tip: EXTRACT(dow) returns 0 for Sunday in Postgres but 1 for Sunday in other dialects. A common source of off-by-one bugs.
Interval arithmetic
Syntax: 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 hardcoded dates. Use cases: 7-day retention window from signup; events within 30 minutes of each other; subscription expiration dates; sliding time windows for aggregation. Interview tip: Postgres uses INTERVAL syntax. BigQuery uses DATE_ADD(date, INTERVAL n DAY). Know both forms.
Time zone handling
Syntax: event_timestamp AT TIME ZONE 'America/New_York'. Converts timestamps between time zones. Critical when data stores UTC but the business question asks about local time ('orders placed before 9am ET'). Use cases: convert UTC to local business hours; daily metrics in a specific timezone; DST transitions; compare user activity across regions. Interview tip: if a question mentions 'business hours' or 'local time', you need to convert from UTC. Forgetting this is a silent bug.
Why date questions are harder than they look
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 attention to detail.
Consider 'calculate monthly active users'. 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 are all different in Postgres, BigQuery, Snowflake, and MySQL. Be fluent in one dialect and aware of the differences in others.
Date questions also compound with other skills. Retention 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.
Practice problems
Five problems at interview difficulty. Each tests a different date function pattern.
28-day retention by signup cohort
users (user_id, signup_date) and events (user_id, event_date). Calculate the percentage of users who were active on exactly day 1, 7, 14, and 28 after signup. Group by signup month. Hint: DATE_TRUNC signup_date to month for cohort grouping. DATE_DIFF between event_date and signup_date to bucket activity into the right day buckets.
Gaps in daily activity
logins (user_id, login_date). Find users who had a gap of 7+ days between consecutive logins. Return user_id, gap_start, gap_end, gap_length_days. Hint: LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) gives the previous login, then compute the difference.
Weekly active users by quarter
events (user_id, event_timestamp). Compute average weekly active users (WAU) for each quarter in 2024. A user is 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.
Business hours only
support_tickets (ticket_id, created_at in UTC, region). Average resolution time in business hours only (9am-6pm local time, Mon-Fri). region maps to a timezone. Hint: convert created_at to local time using AT TIME ZONE. EXTRACT(dow) and EXTRACT(hour) to filter to business hours. Multi-step.
Month-over-month revenue with complete months
orders (order_id, order_date, amount). Compute MoM revenue growth percentage. Exclude the current (incomplete) month. Handle months with zero revenue. Hint: DATE_TRUNC to month, then LAG to get previous month revenue. Filter out the current month with DATE_TRUNC(order_date) < DATE_TRUNC(CURRENT_DATE). COALESCE for zero-revenue months.
Worked example: monthly signup cohort retention at day 7
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.
Date functions FAQ
Why are date function questions harder than they look?+
Which SQL date functions appear most in interviews?+
How do I handle different SQL dialects for date functions?+
What's the best way to practice SQL date functions?+
Write retention queries under time pressure
- 01
Active recall beats re-reading by 50%
Cognitive-science meta-reviews (Dunlosky et al., 2013) rank practice testing as a top-tier study technique, while re-reading and highlighting rank near the bottom
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
Five problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition