Skip to content
Onsite
28 / 44

Daily order counts with zero-filled missing days

MediumP2Coding

Table orders:

ColumnType
idint (primary key)
created_attimestamp

Return the number of orders for every calendar day from the day of the first order to the day of the last order, including days with no orders (count 0). Order by day.

Output columns: day (date), order_count.

Schema

CREATE TABLE orders (
  id int PRIMARY KEY,
  created_at timestamp NOT NULL
);

Example 1

Input
orders on 2024-01-01 (two) and 2024-01-04 (one)
Output
(2024-01-01, 2), (2024-01-02, 0), (2024-01-03, 0), (2024-01-04, 1)

Constraints

  • PostgreSQL dialect.

Hints

PostgreSQL
Run checks the 3 visible tests, Submit all 6.