SQL date and time interview questions with SQLite answers

All data below is synthetic.

Minutes 0 to 8: grouping and the time-zone trap

Q1. Count orders per calendar month.

Q2. The orders table stores `created_utc`. The business reports in IST. Count orders per IST day.

CREATE TABLE orders (order_id INTEGER, created_utc TEXT, amount INTEGER);
INSERT INTO orders VALUES
 (1,'2026-08-31 20:10:00',1500),
 (2,'2026-08-31 23:45:00', 800),
 (3,'2026-09-01 04:00:00',2200),
 (4,'2026-09-01 10:30:00', 650),
 (5,'2026-09-30 19:00:00', 990);

-- Q1: per month, naively in UTC
SELECT strftime('%Y-%m', created_utc) AS month_utc, COUNT(*) AS orders
FROM orders GROUP BY month_utc ORDER BY month_utc;

-- Q2: per IST day (IST is UTC+5:30)
SELECT date(created_utc, '+5 hours', '+30 minutes') AS ist_day, COUNT(*) AS orders
FROM orders GROUP BY ist_day ORDER BY ist_day;

In UTC, August has two orders. In IST, orders 1 and 2 happened on 1 September (20:10 UTC is 01:40 IST), and order 5 happened on 1 October. So the month totals change too. Checking: do you ask what zone the column is in before writing GROUP BY. A candidate who raises this unprompted is usually ahead of the room.

Minutes 8 to 15: intervals and periods

Q3. Days from signup to first purchase, per user.

CREATE TABLE users (user_id INTEGER, signup_date TEXT);
CREATE TABLE purchases (user_id INTEGER, purchase_date TEXT);
INSERT INTO users VALUES (1,'2026-07-01'),(2,'2026-07-03'),(3,'2026-07-10');
INSERT INTO purchases VALUES (1,'2026-07-04'),(1,'2026-07-20'),(2,'2026-08-02');

SELECT u.user_id,
       CAST(julianday(MIN(p.purchase_date)) - julianday(u.signup_date) AS INTEGER) AS days_to_first
FROM users u LEFT JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.user_id, u.signup_date ORDER BY u.user_id;

User 3 returns NULL, not 0. Say so: a NULL here means "has not purchased", and averaging days-to-first-purchase over only the purchasers is a biased number. Checking: whether you notice who is missing from a duration metric.

Q4. Map each date to its Indian financial year label.

CREATE TABLE invoices (inv TEXT, inv_date TEXT);
INSERT INTO invoices VALUES ('A','2026-03-31'),('B','2026-04-01'),('C','2027-02-15');

SELECT inv, inv_date,
       CASE WHEN CAST(strftime('%m', inv_date) AS INTEGER) >= 4
            THEN strftime('%Y', inv_date) || '-' || substr(CAST(strftime('%Y', inv_date) AS INTEGER) + 1, 3, 2)
            ELSE (CAST(strftime('%Y', inv_date) AS INTEGER) - 1) || '-' || substr(strftime('%Y', inv_date), 3, 2)
       END AS fy
FROM invoices;

31 March 2026 is FY 2025-26; 1 April 2026 starts FY 2026-27; 15 February 2027 is still FY 2026-27. Checking: whether you handle the boundary month, which is where hand-written fiscal logic usually breaks.

Q5. Week beginning Monday for each date.

date(d, '-6 days', 'weekday 1') gives the Monday on or before d. The weekday 1 modifier moves forward to the next Monday unless the date already is one; stepping back six days first makes it land on the current week's Monday. Expect a follow-up: "Why not strftime('%W')?" Because a week number resets each January and a week that spans 31 December splits into two labels. Grouping by the Monday date avoids that.

Minutes 15 to 23: comparisons across periods

Q6. Month-to-date revenue compared with the same days of last month.

CREATE TABLE sales (sale_date TEXT, amount INTEGER);
INSERT INTO sales VALUES
 ('2026-08-03',1000),('2026-08-10',1500),('2026-08-25',4000),
 ('2026-09-02',1200),('2026-09-09',1800);

WITH params AS (SELECT '2026-09-10' AS today)
SELECT
  SUM(CASE WHEN sale_date BETWEEN date(today,'start of month') AND today
           THEN amount ELSE 0 END) AS mtd,
  SUM(CASE WHEN sale_date BETWEEN date(today,'start of month','-1 month')
                              AND date(today,'-1 month')
           THEN amount ELSE 0 END) AS same_days_last_month
FROM sales, params;

September to the 10th is ₹3,000; 1–10 August is ₹2,500. Comparing MTD with the full previous month (₹6,500) is the mistake this question exists to catch. Mention the edge case: on 31 March, "one month ago" is not a clean date, and SQLite normalises 31 February forward to early March. State your rule for month-end.

Minutes 23 to 30: missing days and cohorts

Q7. Show daily signups for 1–7 September, including days with zero.

CREATE TABLE signups (signup_date TEXT);
INSERT INTO signups VALUES ('2026-09-01'),('2026-09-01'),('2026-09-03'),('2026-09-06');

WITH RECURSIVE cal(d) AS (
  SELECT '2026-09-01'
  UNION ALL SELECT date(d, '+1 day') FROM cal WHERE d < '2026-09-07')
SELECT cal.d AS day, COUNT(s.signup_date) AS signups
FROM cal LEFT JOIN signups s ON s.signup_date = cal.d
GROUP BY cal.d ORDER BY cal.d;

A chart built from the raw table would draw a straight line from 3 to 6 September and hide two zero days. Checking: the calendar-table idea itself, and COUNT(s.signup_date) rather than COUNT(*), which would report 1 for empty days.

Q8. Why does `WHERE order_date >= '2026-9-5'` return odd results?

SQLite has no date type; dates are text, and '2026-9-5' sorts after '2026-10-01' because the character 9 is greater than 1. Dates must be stored as zero-padded ISO strings (YYYY-MM-DD) for comparisons and sorting to work. In other databases the same question becomes "what happens when you compare a timestamp column to a date literal?" — the literal becomes midnight, so <= '2026-09-05' excludes everything after 00:00 on the 5th.

What to rehearse before the round

• Say the time zone of every timestamp you touch.
• For any period comparison, state both window boundaries aloud, including whether they are inclusive.
• For any daily or weekly chart, ask whether missing periods should appear as zero.
• For any duration metric, say who is excluded because the end event has not happened yet, and whether a median would describe the typical user better than a mean.
• When a timestamp carries seconds, check whether "same day" logic compares dates or datetimes; mixing the two is how a filter drops the last day of a month.
• Know one dialect well and name its equivalents: SQLite strftime/date, PostgreSQL date_trunc/interval, MySQL DATE_FORMAT/DATE_ADD.

The same care with dates shows up in window function questions, where LAG over a month with missing rows gives the wrong comparison. Period-over-period reporting on Indian financial-year data is part of the Data Analyst course.