-- USER_INFO: USER_ID, GENDER, AGE, JOINED
-- ONLINE_SALE: ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
WITH 2021_USERS AS (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
),
TOTAL_2021_USERS AS (
SELECT COUNT(*) AS TOTAL
FROM 2021_USERS
)
SELECT
YEAR(O.SALES_DATE) AS YEAR,
MONTH(O.SALES_DATE) AS MONTH,
COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
ROUND(
COUNT(DISTINCT O.USER_ID) /
(SELECT TOTAL FROM TOTAL_2021_USERS),
1
) AS PURCHASED_RATIO
FROM 2021_USERS AS U
JOIN ONLINE_SALE AS O
ON U.USER_ID = O.USER_ID
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY 1, 2;