Amazon SQL Interview: “Finding user purchases”

Photo by Karolina Grabowska on Pexels

The SQL interview question that we will be discussing involves joining tables to extract and compare information based on specific criteria. Solving such SQL question require a good understanding of SQL joins, as well as the ability to write efficient and effective queries, which would not crash in face of massive datasets.

Here are the codes:

SELECT DISTINCT at1.user_id
FROM amazon_transactions at1
JOIN amazon_transactions at2
ON at1.user_id = at2.user_id
WHERE at1.id != at2.id
AND ABS(at1.created_at - at2.created_at) <= 7

And here one can find the full explanation of the solution: