Drill 3 of 12 in E-commerce questions
0

Interview Question Bank · NOT EXISTS · NOT IN and NULLs · date ranges

3. Products not sold in Q1

EasyE-commerceQuestion #87

Concept: NOT EXISTS · NOT IN and NULLs · date rangesshow

'Never happened in a period' is an anti-join: keep rows from the main table for which NOT EXISTS (a matching row in the period). Avoid NOT IN on a column that can be NULL: x NOT IN (1, 2, NULL) is never true, so the whole query silently returns nothing. For a date range, >= the first day and < the day after the last day is the safest boundary.

Example (different data):

SELECT b.title
FROM books b
WHERE NOT EXISTS (
  SELECT 1 FROM loans l
  WHERE l.book_id = b.id
    AND l.loaned_on >= '2025-06-01' AND l.loaned_on < '2025-07-01'
);

Your task

Quillmere's merchandiser is clearing shelf space and wants every catalog product that nobody bought in Q1 2025.

Return name and category of every product in shop_products that was not in any completed order placed from 2025-01-01 to 2025-03-31 (both days included). Sort by category, then name.

Starting Postgres in your browser…

Checking your access…