Amazon SQL Interview: “Finding user purchases”
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: