Weekly Seller Signups

A handmade-goods marketplace wants a weekly chart of new seller signups. Weeks run Monday to Sunday and are labelled by the date of their Monday.

Return one row per week from the week of the earliest signup through the week of the latest signup, inclusive, with columns week_start (the Monday, text 'YYYY-MM-DD') and signups (sellers who joined that week). Weeks inside that range with no signups must appear with 0, so the chart has no holes. A week that crosses New Year is still one week.

Order by week_start. If there are no sellers, return no rows.

Tables

sellers — One row per seller account on the marketplace.

Example

sellers

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Dates & Time · problem 48 of 70 · SQLite

The idea

Two pieces are needed: a weekly bucket for each signup, and a complete calendar of weeks to report on.

Bucketing. SQLite has no DATE_TRUNC, but its date modifiers do the job. 'weekday 0' moves a date forward to the next Sunday, or leaves it alone if it is already Sunday; '-6 days' then lands on that week's Monday. A Sunday-night signup stays in its own week, and a week that crosses New Year keeps one label because it is keyed by a full date, not by a year-and-week-number pair.

Calendar. A WITH RECURSIVE CTE starts at the earliest Monday and adds '+7 days' until it reaches the latest Monday. HAVING COUNT(*) > 0 stops the anchor from emitting a NULL week when the table is empty.

Counting. LEFT JOIN the bucketed signups onto the calendar and use COUNT(s.week_start), which counts only matched rows, so empty weeks show 0.

The alternative finds the Monday with strftime('%w') arithmetic and counts with a correlated subquery for each generated week.

Solution

WITH RECURSIVE signup_weeks AS (
  SELECT date(joined_at, 'weekday 0', '-6 days') AS week_start
  FROM sellers
),
calendar(week_start) AS (
  SELECT MIN(week_start)
  FROM signup_weeks
  HAVING COUNT(*) > 0
  UNION ALL
  SELECT date(week_start, '+7 days')
  FROM calendar
  WHERE week_start < (SELECT MAX(week_start) FROM signup_weeks)
)
SELECT c.week_start,
       COUNT(s.week_start) AS signups
FROM calendar AS c
LEFT JOIN signup_weeks AS s ON s.week_start = c.week_start
GROUP BY c.week_start
ORDER BY c.week_start;

Another way

WITH RECURSIVE mondays AS (
  SELECT date(joined_at,
              '-' || ((CAST(strftime('%w', joined_at) AS INTEGER) + 6) % 7) || ' days') AS monday
  FROM sellers
),
bounds AS (
  SELECT MIN(monday) AS first_week,
         CAST((julianday(MAX(monday)) - julianday(MIN(monday))) / 7 AS INTEGER) AS last_n
  FROM mondays
),
n(i) AS (
  SELECT 0 FROM bounds WHERE first_week IS NOT NULL
  UNION ALL
  SELECT i + 1 FROM n, bounds WHERE i < bounds.last_n
)
SELECT date(b.first_week, '+' || (7 * n.i) || ' days') AS week_start,
       (SELECT COUNT(*) FROM mondays AS m
        WHERE m.monday = date(b.first_week, '+' || (7 * n.i) || ' days')) AS signups
FROM n, bounds AS b
ORDER BY week_start;

Why common answers fail

Groups the existing signups only, so weeks with zero signups are missing from the chart.

SELECT date(joined_at, 'weekday 0', '-6 days') AS week_start, COUNT(*) AS signups
FROM sellers
GROUP BY week_start
ORDER BY week_start;

'weekday 1' moves forward to the next Monday, so every non-Monday signup lands in the following week.

WITH RECURSIVE signup_weeks AS (
  SELECT date(joined_at, 'weekday 1') AS week_start FROM sellers
), calendar(week_start) AS (
  SELECT MIN(week_start) FROM signup_weeks HAVING COUNT(*) > 0
  UNION ALL
  SELECT date(week_start, '+7 days') FROM calendar
  WHERE week_start < (SELECT MAX(week_start) FROM signup_weeks)
)
SELECT c.week_start, COUNT(s.week_start) AS signups
FROM calendar AS c LEFT JOIN signup_weeks AS s ON s.week_start = c.week_start
GROUP BY c.week_start ORDER BY c.week_start;

With no sellers, MIN() still returns one NULL row, so the report shows a bogus (NULL, 0) week instead of nothing.

WITH RECURSIVE signup_weeks AS (
  SELECT date(joined_at, 'weekday 0', '-6 days') AS week_start FROM sellers
), calendar(week_start) AS (
  SELECT MIN(week_start) FROM signup_weeks
  UNION ALL
  SELECT date(week_start, '+7 days') FROM calendar
  WHERE week_start < (SELECT MAX(week_start) FROM signup_weeks)
)
SELECT c.week_start, COUNT(s.week_start) AS signups
FROM calendar AS c LEFT JOIN signup_weeks AS s ON s.week_start = c.week_start
GROUP BY c.week_start ORDER BY c.week_start;

Starts the 7-day buckets on the first signup date instead of on a Monday, so the weeks are not Monday-to-Sunday.

WITH RECURSIVE calendar(week_start) AS (
  SELECT MIN(date(joined_at)) FROM sellers HAVING COUNT(*) > 0
  UNION ALL
  SELECT date(week_start, '+7 days') FROM calendar
  WHERE week_start <= (SELECT MAX(date(joined_at)) FROM sellers)
)
SELECT c.week_start, COUNT(s.seller_id) AS signups
FROM calendar AS c
LEFT JOIN sellers AS s
  ON date(s.joined_at) >= c.week_start AND date(s.joined_at) < date(c.week_start, '+7 days')
WHERE c.week_start <= (SELECT MAX(date(joined_at)) FROM sellers)
GROUP BY c.week_start ORDER BY c.week_start;

What the interviewer is checking

Tests date bucketing without DATE_TRUNC and whether you remember that a GROUP BY cannot produce rows for periods with no data.

Mistakes to avoid

• Using 'weekday 1', which rounds forward to the next Monday instead of back.
• Forgetting zero-signup weeks.
• Labelling weeks with strftime('%W') plus the year, which splits the New Year week in two.
• COUNT(*) after a LEFT JOIN counts empty weeks as 1.

Follow-ups to expect

• Add a running total of sellers per week (SUM(signups) OVER (ORDER BY week_start)).
• Use Sunday-to-Saturday weeks instead (date(joined_at, 'weekday 6', '-6 days')).
• Report a fixed range, such as the last 12 weeks before today, even if it has no signups at all (anchor the recursion on date('now') instead of MIN).

In real work

Weekly growth charts, sprint reports and capacity plans need continuous time buckets, and "missing zero weeks" is one of the most common charting bugs.

Practice next