[SQL] “Finding user purchases” – Amazon


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:

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:

Related Images: