How do you approach SQL: Date x Product Matrix (CROSS JOIN)?

CROSS JOIN produces every combination of rows from two sides - the cartesian product.

What are common mistakes on SQL: Date x Product Matrix (CROSS JOIN)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Date x Product Matrix (CROSS JOIN)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. You have a small `Products(id, name)` table and a list of dates (e.g. from a `generate_series` or a calendar table). To build a "zero-filled" daily-sales report, you need every (date, product) pair even where there were no sales - the classic CROSS JOIN pattern. Given `dates` (array of ISO date strings, sorted) and `products` (array of `{id, name}`), return the cartesian product as an array of `{date, product_id, product_name}` objects, sorted by date ascending then product_id ascending. **For the auto-grader:** implement `dateProductMatrix(dates, products)`.

Example 1

Input: dates = ["2026-01-01","2026-01-02"], products = [{id:1,name:"A"},{id:2,name:"B"}]

Output: [{date:"2026-01-01",product_id:1,product_name:"A"},{date:"2026-01-01",product_id:2,product_name:"B"},{date:"2026-01-02",product_id:1,product_name:"A"},{date:"2026-01-02",product_id:2,product_name:"B"}]

Constraints

- 0 <= dates.length, products.length <= 100 - Total output size <= 10^4

SQL: Date x Product Matrix (CROSS JOIN): approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 3-step hint ladder

CROSS JOIN produces every combination of rows from two sides - the cartesian product.
Postgres `generate_series('2026-01-01'::date, '2026-01-10', '1 day')` generates a date axis. Then CROSS JOIN it with Products.
LEFT JOIN the matrix back to actual sales to get zero-filled daily reports.

The optimal approach

Show the approach: Canonical SQL (Postgres) - zero-filled daily product report:

```sql

SELECT d.day::date AS date, p.id AS product_id, p.name AS product_name,

COALESCE(SUM(s.qty), 0) AS qty

FROM generate_series('2026-01-01'::date, '2026-01-31', '1 day') d(day)

CROSS JOIN Products p

LEFT JOIN Sales s ON s.product_id = p.id AND s.sale_date = d.day

GROUP BY d.day, p.id, p.name

ORDER BY d.day, p.id;

```

The CROSS JOIN guarantees every (date, product) pair exists; the LEFT JOIN to Sales lets days with no sale show 0 instead of being missing entirely. This pattern is essential for charts and time-series reports - without it, a day with zero sales just doesn't appear.

Performance: CROSS JOIN explodes row count multiplicatively. Always bound the date range and products explicitly.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.