SQL Date and Time Functions Practice
Product and analytics interviews love dates. Practise date arithmetic, grouping by month, comparing periods, finding consecutive days and building retention and cohort reports.
20 questions · 8 Easy · 8 Medium · 4 Hard · about 4 hours · MySQL & PostgreSQL
What you'll practise
- Date arithmetic and intervals
- Grouping by day, week or month
- Month-over-month and week-over-week
- Consecutive-day streaks
- Retention and cohorts
Learn the concepts first
Questions in this plan
Daily Active Users (DAU)
Compute the daily count of unique active users from raw product activity event timestamps.
Rising Temperature
Find all dates where the temperature was higher than the previous day using DATEDIFF.
Active Users in Last 7 Days
Filter rolling active accounts by comparing timestamp with CURRENT_DATE interval arithmetic.
Amazon: Late Shipments Audit
Identify orders delivered past their guaranteed delivery date and calculate the number of days late.
Listing Booking Windows
Count each listing's bookings in the last 90 days, the last 365 days and all time.
Monthly Listing Ratings
Average star rating for each listing in each month.
Valid Promotion Order Share
Percentage of all orders that used a promotion that was actually valid on the order date.
Composer Post Rate by App
Yesterday's share of opened composers that ended in a published post, per app.
Month-over-Month Revenue Growth
Compute monthly successful payment volume and its percentage change versus the prior month.
Amazon: Days Between Repeat Orders
Calculate the average gap in days between consecutive purchases for customers with repeat orders.
Double-Booked Listings
Detect overlapping reservations on the same listing and measure the overlap.
Average Days Between Orders
Measure each repeat customer's average gap between consecutive orders.
Advocate Program Long-Call Growth
Measure month-over-month growth in the number of long support calls.
Sales on Registration Day
Measure what percentage of all sales happened on the same calendar day the customer registered.
Drivers With Back-to-Back Drops
Flag drivers whose weekly completed trips fell by 30% or more two weeks in a row.
Month-over-Month Retained Users
Count users who were active in a month and also active in the month before.
Amazon: Monthly Cohort Retention
Analyze customer retention cohorts across 1-month and 2-month subsequent repurchase cycles.
Longest Login Streaks
Find users who logged in on at least three consecutive days and report their best streak.
User Session Reconstruction
Group raw click events into sessions using a 30-minute inactivity rule.
Next-Month Cohort Retention
For each signup-month cohort, measure how many users were active in the following calendar month.