- Write a query that finds the top 3 users with most active (frequency) on buying for each group 17-22 and 23-30
WITH BASE1 AS (
SELECT A.*,
A.Saham_invested_amount+A.Campuran_invested_amount+A.Pasar_Uang_invested_amount+A.Pendapatan_Tetap_invested_amount AS Total_Investment
from transaction A) -- Select transaction table with users' total investment
,BASE2 AS (
SELECT B.*,
coalesce(LAG(B.TOTAL_INVESTMENT) OVER(PARTITION BY USER_ID ORDER BY B.DATE ASC),B.TOTAL_INVESTMENT) AS PREVIOUS_DAY_INVESTMENT,
TOTAL_INVESTMENT - coalesce(LAG(B.TOTAL_INVESTMENT) OVER(PARTITION BY USER_ID ORDER BY B.DATE ASC),B.TOTAL_INVESTMENT) AS BUY_SELL
FROM BASE1 B) -- Select transaction table with users' previous investment and users' buy/sell amount columns
,BUY_SELL_ACTIVITY AS (
SELECT C.*,
CASE WHEN BUY_SELL > 0 THEN 1 ELSE 0 END AS FLAG_BUY,
CASE WHEN BUY_SELL < 0 THEN 1 ELSE 0 END AS FLAG_SELL
FROM BASE2 C) -- Select transaction table with flag buy and sell
,DEMOGRAPHY_AGE_17_30 AS (
SELECT T.*,
CASE WHEN T.user_age >=17 AND T.user_age <= 22 THEN '17-22'
WHEN T.user_age >=23 AND T.user_age <= 30 THEN '23-30'
ELSE NULL
END AS USER_AGE_DISTRIBUTION
FROM demography T
WHERE CASE WHEN T.user_age >=17 AND T.user_age <= 22 THEN '17-22'
WHEN T.user_age >=23 AND T.user_age <= 30 THEN '23-30'
ELSE NULL END IS NOT NULL ) -- Select demography table filtered by age group 17-22 and 23-30
, FINAL_DATA AS (
SELECT T.*,
ROW_NUMBER()OVER(PARTITION BY USER_AGE_DISTRIBUTION ORDER BY COUNT_BUY DESC) AS RN
FROM (
SELECT DMG.USER_ID,
DMG.USER_AGE_DISTRIBUTION,
SUM(FLAG_BUY) AS COUNT_BUY
FROM BUY_SELL_ACTIVITY BSA
JOIN DEMOGRAPHY_AGE_17_30 DMG ON BSA.USER_ID = DMG.USER_ID
GROUP BY USER_AGE_DISTRIBUTION,USER_ID ) T
) -- Select table with user_id, user age distribution, total count of user buy activity, and row number groupped by age 17-22 and 23-30 and ordered by the total user buy activity descending
SELECT * FROM FINAL_DATA
where rn <= 3
order by USER_AGE_DISTRIBUTION asc -- Select top 3 users with most buying for each group 17-22 and 23-30
- Write a query that finds the top 3 users with most active (frequency) on selling (Reksadana Saham Portfolio Only) who are female and income source not from "Keuntungan Bisnis"
WITH BASE1 AS (
SELECT A.*,
coalesce(LAG(A.Saham_invested_amount) OVER(PARTITION BY USER_ID ORDER BY A.DATE ASC),A.Saham_invested_amount) AS PREVIOUS_DAY_SAHAM_INVESTMENT,
Saham_invested_amount - coalesce(LAG(A.Saham_invested_amount) OVER(PARTITION BY USER_ID ORDER BY A.DATE ASC),A.Saham_invested_amount) AS BUY_SELL
FROM TRANSACTION A) -- Select transaction table with users' total saham investment and users buy/sell amount
,BUY_SELL_SAHAM_ACTIVITY AS (
SELECT B.*,
CASE WHEN BUY_SELL > 0 THEN 1 ELSE 0 END AS FLAG_BUY,
CASE WHEN BUY_SELL < 0 THEN 1 ELSE 0 END AS FLAG_SELL
FROM BASE1 B) -- Select transaction table with flag buy and sell
,DEMOGRAPHY_FEMALE_NOT_FROM_KEUNTUNGAN_BISNIS AS (
SELECT * FROM demography T
WHERE T.user_gender = 'Female'
AND T.user_income_source <> 'Keuntungan Bisnis' ) -- Select demography table filtered by female gender and income source not from keuntungan bisnis
, FINAL_DATA AS (
SELECT DMG.USER_ID,
DMG.USER_GENDER,
DMG.USER_INCOME_SOURCE,
SUM(FLAG_SELL) AS COUNT_SELL
FROM BUY_SELL_SAHAM_ACTIVITY BSA
JOIN DEMOGRAPHY_FEMALE_NOT_FROM_KEUNTUNGAN_BISNIS DMG ON BSA.USER_ID = DMG.USER_ID
GROUP BY USER_GENDER,USER_INCOME_SOURCE,USER_ID ) -- Select table with user_id, user gender, user income source, total count of users' sell saham activity
SELECT * FROM FINAL_DATA
ORDER BY COUNT_SELL DESC
LIMIT 3 -- Select top 3 users with most selling on saham who are female and income source not from keuntungan bisnis