Create a new table based on another two tables in SQL. The first table USER has one variable
user_id | purchase_time
-----------------------------
Marta | 10/20/2019
Steve | 10/21/2019
Michael | 10/18/2019
And COUPON table has two variables
user_id | coupon_id | coupon_time
-------------------------------------------
Marta | 12345 | 10/21/2019
Steve | 12356 | 10/18/2019
Marta | 12365 | 10/01/2019
Michael | |
The problem is to create a new table USER_SUMMARY. Count the numbers of coupons received before and after purchase_time, and count the total coupons received. If no coupon received after puchase_time, count 0. The final created a table should like below:
user_id | purchase_before_coupon | purchase_after coupon | total_coupons
--------------------------------------------------------------------------------------
Marta | 1 | 1 | 2
Steve | 0 | 1 | 1
Michael | 0 | 0 | 0
Should not be complicated if done in Python or R, but I don't quite know how to do it in SQL syntax. Thanks in advance!
You can use a CREATE ... SELECT query to generate the user_summary table. The SELECT query counts how many purchases were made before or after each coupon for each user, as well as counting the total number of their coupons:
CREATE TABLE user_summary AS
SELECT u.user_id,
COALESCE(SUM(u.purchase_time < c.coupon_time), 0) AS purchase_before,
COALESCE(SUM(u.purchase_time >= c.coupon_time), 0) AS purchase_after,
COUNT(c.coupon_id) AS total
FROM user u
LEFT JOIN coupon c ON c.user_id = u.user_id
GROUP BY u.user_id
Output (of SELECT * FROM user_summary) after running this query:
user_id purchase_before purchase_after total
Marta 1 1 2
Michael 0 0 0
Steve 0 1 1