Drill 11 of 12 in Gaming questions
0

Interview Question Bank · LAG per player and item · interval comparison

11. Same item bought twice within 24 hours

HardGamingQuestion #155

Concept: LAG per player and item · interval comparisonshow

To compare each event with the previous one of the same kind, partition by everything that defines 'same' (person and item) and use LAG(timestamp). Subtracting two timestamps gives an interval you can compare directly with INTERVAL '24 hours', which is exact to the second, unlike comparing calendar dates. A self-join on 'within 24 hours' finds every pair in the window, not just consecutive ones, so it over-counts when there are three or more events.

Example (different data):

WITH c AS (
  SELECT patient_id, test, taken_at,
    LAG(taken_at) OVER (PARTITION BY patient_id, test ORDER BY taken_at) AS prev_at
  FROM lab_tests
)
SELECT * FROM c WHERE taken_at - prev_at <= INTERVAL '48 hours';

Your task

Skyrift Arena's store team suspects confusing buttons cause accidental double purchases and wants every case of a player buying the same item again soon after. Refunded purchases are already handled and should be ignored.

Ignoring refunded purchases, find every purchase made at most 24 hours after the same player's previous purchase of the same item (exactly 24 hours counts). Return username, item, first_purchase_at (the previous purchase) and repeat_purchase_at. Sort by repeat_purchase_at, then username.

Starting Postgres in your browser…

Checking your access…