Блог

Создайте новую таблицу на основе еще двух таблиц в SQL. У первого пользователя таблицы есть одна переменная

 user_id     | purchase_time  
-----------------------------
Marta       | 10/20/2019             
Steve       | 10/21/2019            
Michael     | 10/18/2019           
  

И таблица КУПОНОВ имеет две переменные

 user_id     | coupon_id        | coupon_time 
-------------------------------------------
Marta       | 12345            | 10/21/2019 
Steve       | 12356            | 10/18/2019 
Marta       | 12365            | 10/01/2019 
Michael     |                  |  
  

Проблема заключается в создании новой таблицы USER_SUMMARY. Подсчитайте количество купонов, полученных до и после purchase_time, и подсчитайте общее количество полученных купонов. Если после puchase_time купон не получен, считайте 0. Окончательная созданная таблица должна выглядеть следующим образом:

 user_id     | purchase_before_coupon        | purchase_after coupon    | total_coupons 
--------------------------------------------------------------------------------------
Marta       | 1                             | 1                        | 2                        
Steve       | 0                             | 1                        | 1
Michael     | 0                             | 0                        | 0
  

Это не должно быть сложно, если сделано на Python или R, но я не совсем понимаю, как это сделать в синтаксисе SQL. Заранее спасибо!

Вы можете использовать CREATE ... SELECT запрос для создания user_summary таблицы. SELECT Запрос подсчитывает, сколько покупок было совершено до или после каждого купона для каждого пользователя, а также подсчитывает общее количество их купонов:

 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
  

Вывод (of SELECT * FROM user_summary ) после выполнения этого запроса:

 user_id     purchase_before     purchase_after  total
Marta       1                   1               2
Michael     0                   0               0
Steve       0                   1               1
  

Демонстрация на db-fiddle