GROUP BY Multiple Columns
Group data by multiple columns to get more granular insights. Calculate the number of invoices and total revenue by...
GROUP BYMultiple ColumnsDate FunctionsEXTRACT
Beginner
7m15 pts
1393%
SQL Practice Hub
SQL Date and Time Functions Practice Questions
Answer "by month", "last 30 days" and "days between" questions correctly.
15 questionsFreeRuns in your browser
- DATE_TRUNC
- Snaps a timestamp to the month, week or day.
- EXTRACT / TO_CHAR
- Pulls out or formats part of a date.
- Intervals
- Do date arithmetic instead of subtracting strings.
What you will practice
- Group by month or week by truncating the timestamp instead of formatting it as text
- Pull out a year, month, quarter or weekday with EXTRACT for cohort and seasonality analysis
- Add and subtract intervals to build rolling windows such as the last 7 or 30 days
- Measure the gap between two timestamps in days, hours or months
- Format dates for output with TO_CHAR while keeping the underlying column sortable
Read the full overview
Almost every reporting question is a date question: revenue by month, signups in the last 30 days, days between an order and its delivery. Getting it right means truncating timestamps to the right grain, extracting parts with EXTRACT, formatting with TO_CHAR, and doing arithmetic with intervals rather than subtracting raw strings. These questions work with real timestamp columns so the edge cases, month boundaries and time zones behave the way they do in production.
15 questions
Month-over-Month Revenue Growth Rate
Finance needs a revenue growth analysis showing monthly revenue alongside the previous month and the percentage growth...
CTEWindow FunctionsLAGDate Functions+1Advanced10m35 pts5100%Cumulative Revenue by Country Over Time
The finance team needs a cumulative revenue growth chart for key markets. Show monthly revenue alongside the running...
CTEWindow FunctionsRunning TotalPARTITION BY+1Advanced10m35 pts267%Customer Purchase Gap Analysis
The retention team wants to understand purchase frequency patterns. Calculate the average, minimum, and maximum gap (in...
CTEWindow FunctionsLAGDate Functions+1Advanced12m40 pts2100%Year-over-Year Genre Revenue Change
The analytics team wants to track how genre popularity changes over time. Calculate the year-over-year revenue change...
CTEWindow FunctionsLAGPARTITION BY+2Advanced12m40 pts0—Monthly Invoice Trend (2012)
Report the month-by-month invoice activity for fiscal year 2012. Schema: Invoice ColumnType InvoiceIdINTEGER (PK)...
WHEREEXTRACTTO_CHARGROUP BY+4Intermediate8m20 pts2100%Customers Active in Multiple Years
Find customers who have purchased across multiple calendar years — a proxy for loyalty. Schema: Customer ColumnType...
GROUP BYHAVINGCOUNT DISTINCTEXTRACT+3Intermediate10m25 pts2100%Employee Tenure Bracket
Compute each employee's tenure (years between HireDate and the fixed reference date 2026-06-12) and bucket them by...
EXTRACTDate ArithmeticCASE WHENORDER BYIntermediate8m20 pts9100%Quarterly Genre Sales (2012-2013)
Show quarter-by-quarter revenue per genre across calendar years 2012 and 2013 for the four largest genres. Schema:...
EXTRACTQUARTERJOINMultiple Tables+3Intermediate12m30 pts1100%First-Purchase Cohort Count by Month
Show how many customers made their first-ever purchase each month — the new-customer cohort series. Schema: Invoice...
SubqueryGROUP BYMINTO_CHAR+3Intermediate10m25 pts267%Monthly Cohort Retention
Compute cohort retention: for each customer signup-month (first invoice), how many of that cohort were active in month...
CTEMultiple CTEsWindow FunctionsCohort Analysis+5Advanced15m40 pts0—Month-over-Month Revenue & Top Customer
Produce a monthly report combining MoM revenue growth with the top-spending customer in that month. Schema: Customer...
CTEMultiple CTEsWindow FunctionsLAG+3Advanced15m40 pts8100%LTV Quartile & Recency Analysis
Segment customers into quartiles by lifetime value and compute days since their most recent purchase. Schema: Customer...
CTENTILEWindow FunctionsDate Arithmetic+2Advanced15m40 pts8100%Shortest Inter-Purchase Gap per Customer
For each customer, compute their shortest gap (in days) between any two consecutive purchases. This surfaces the most...
CTEWindow FunctionsLAGDate Arithmetic+3Advanced15m40 pts7100%Day-of-Week Revenue Pivot
Pivot revenue per billing country across days of the week — useful for spotting weekend-heavy markets. Schema: Invoice...
CTEEXTRACTDOWConditional Aggregation+5Advanced15m40 pts8100%
FAQ
SQL Date and Time Functions practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
How do I group by month?
Truncate the timestamp to the month, then group by the result.
- DATE_TRUNC('month', ts) returns the first day of that month.
- Group and order by the same expression (GROUP BY 1 works in PostgreSQL).
- EXTRACT(MONTH ...) gives only the month number, which mixes different years together.
SQL
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;How do I get the last 30 days or the days between two dates?
Subtract dates for a day count, and compare against CURRENT_DATE minus an interval.
- In PostgreSQL, date minus date gives a whole number of days.
- timestamp minus timestamp gives an interval.
- Compare the column to a computed bound; avoid wrapping the column in a function so indexes still work.
SQL
SELECT order_id,
delivered_at::date - ordered_at::date AS days_to_deliver
FROM orders
WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days';Why are my date boundaries and time zones off?
Timestamps are stored in UTC and shown in your session time zone, so day and month boundaries can shift.
- Convert first with AT TIME ZONE 'Asia/Kolkata', then truncate.
- Use >= start and < next day or month; BETWEEN includes both ends and can catch midnight of the next day.
- Check the session time zone when results differ between tools.
SQL
SELECT DATE_TRUNC('day',
created_at AT TIME ZONE 'Asia/Kolkata') AS day,
COUNT(*) AS orders
FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01'
GROUP BY 1;Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.