/*
Amazon SQL Interview Question

You have a table of in-app purchases by user. Users that make their first in-app purchase are placed in a marketing campaign where they see call-to-actions for more in-app purchases.

Find the number of users that made additional in-app purchases due to the success of the marketing campaign.

The marketing campaign doesn't start until one day after the initial in-app purchase so users that only made one or multiple purchases on the first day do not count, nor do we count users that over time purchase only the products they purchased on the first day.
*/
    
-- Schema Setup
CREATE TABLE in_app_purchases ( created_at DATETIME, price BIGINT, product_id BIGINT, quantity BIGINT, user_id BIGINT);

INSERT INTO in_app_purchases (created_at, price, product_id, quantity, user_id) VALUES('2024-12-01 10:00:00', 500, 101, 1, 1),  ('2024-12-02 11:00:00', 700, 102, 1, 1),('2024-12-01 12:00:00', 300, 103, 1, 2), ('2024-12-03 14:00:00', 400, 103, 1, 2),('2024-12-02 09:30:00', 200, 104, 1, 3), ('2024-12-04 15:30:00', 600, 105, 2, 3),('2024-12-01 08:00:00', 800, 106, 1, 4), ('2024-12-05 18:00:00', 500, 107, 1, 4),('2024-12-06 16:00:00', 700, 108, 1, 5); 

-- Solution
WITH first_purchases AS (
    SELECT 
        user_id,
        MIN(created_at) AS first_purchase,
        product_id
    FROM in_app_purchases
    GROUP BY user_id, product_id
)

SELECT 
    COUNT(DISTINCT a.user_id) AS successful_users
FROM in_app_purchases a
JOIN first_purchases b
ON a.user_id = b.user_id
WHERE DATEDIFF(a.created_at, b.first_purchase) >= 1
AND a.product_id <> b.product_id;