create a new table based on another two tables in mysql

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

Demo on db-fiddle

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top