task Indexes · hard

Design a purchase lookup index

Harbor Market needs the purchases for shopper 42 on January 4, 2025. Create a full index with exactly (shopper_id, event_kind, event_at), in that order, then return id, event_at and amount for those purchases. The submitted SELECT must use an indexed search through that index.

what to return

Columns

idevent_atamount in this order

Rows

6 rows · in the order asked

Watch for

Use event_kind = purchase and the half-open timestamp interval [2025-01-04 00:00:00, 2025-01-05 00:00:00).

Order by event_at, then id, ascending. Choose any index name; do not change source rows.

This controlled archive contains 12,005 rows. Indexed access is the learning objective, not a claim about elapsed execution time.

Skill

Combine two equality predicates and a timestamp range in a composite index. Refresher: Date boundaries & monthly buckets →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.