SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
Create table Visits (user_id int, visit_date date); Create table Transactions (user_id int, transaction_date date, amount int); insert into Visits (user_id, visit_date) values ('1', TO_DATE('2020-01-01', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('2', TO_DATE('2020-01-02', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('12', TO_DATE('2020-01-01', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('19', TO_DATE('2020-01-03', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('1', TO_DATE('2020-01-02', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('2', TO_DATE('2020-01-03', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('1', TO_DATE('2020-01-04', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('7', TO_DATE('2020-01-11', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('9', TO_DATE('2020-01-25', 'yyyy-mm-dd')); insert into Visits (user_id, visit_date) values ('8', TO_DATE('2020-01-28', 'yyyy-mm-dd')); insert into Transactions (user_id, transaction_date, amount) values ('1', TO_DATE('2020-01-02', 'yyyy-mm-dd'), '120'); insert into Transactions (user_id, transaction_date, amount) values ('2', TO_DATE('2020-01-03', 'yyyy-mm-dd'), '22'); insert into Transactions (user_id, transaction_date, amount) values ('7', TO_DATE('2020-01-11', 'yyyy-mm-dd'), '232'); insert into Transactions (user_id, transaction_date, amount) values ('1', TO_DATE('2020-01-04', 'yyyy-mm-dd'), '7'); insert into Transactions (user_id, transaction_date, amount) values ('9', TO_DATE('2020-01-25', 'yyyy-mm-dd'), '33'); insert into Transactions (user_id, transaction_date, amount) values ('9', TO_DATE('2020-01-25', 'yyyy-mm-dd'), '66'); insert into Transactions (user_id, transaction_date, amount) values ('8', TO_DATE('2020-01-28', 'yyyy-mm-dd'), '1'); insert into Transactions (user_id, transaction_date, amount) values ('9', TO_DATE('2020-01-25', 'yyyy-mm-dd'), '99'); WITH data AS ( SELECT DISTINCT t.user_id, t.transaction_date, v.user_id AS visit_user_id, v.visit_date, COUNT(t.transaction_date) OVER (PARTITION BY t.user_id, t.transaction_date) AS transactions_count, COUNT(DISTINCT v.visit_date) OVER (PARTITION BY v.user_id, v.visit_date) AS visits_count FROM Visits v LEFT JOIN Transactions t ON t.user_id = v.user_id AND t.transaction_date= v.visit_date ORDER BY transactions_count ), lvl AS ( SELECT LEVEL-1 AS transactions_count FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(transactions_count)+1 FROM data) ) SELECT l.transactions_count, NVL(SUM(d.visits_count), 0) AS visits_count FROM lvl l LEFT JOIN data d ON l.transactions_count = d.transactions_count GROUP BY l.transactions_count ORDER BY l.transactions_count ; WITH base AS ( -- table that shows per customer per visit, how many transactions were done SELECT A.user_id, COUNT(B.user_id) as tran_cnt FROM Visits A LEFT JOIN Transactions B ON A.user_id = B.user_id AND A.visit_date = B.transaction_date GROUP BY A.user_id, A.visit_date ) SELECT A.transactions_count, IFNULL(B.visits_count, 0) as visits_count FROM ( -- generate # of transaction count from 0 to N SELECT 0 as transactions_count UNION -- using Transactions table here to get upper bound -- bound there cannot be more transaction count than -- total number of transactions there is SELECT ROW_NUMBER() OVER() as transactions_count FROM Transactions ) A LEFT JOIN ( -- table that shows per tran_cnt, how many customer visits SELECT tran_cnt, COUNT(1) as visits_count FROM base GROUP BY tran_cnt ) B ON A.transactions_count = B.tran_cnt -- don't need to show the extra transactions_count beyond MAX(tran_cnt) WHERE A.transactions_count <= (SELECT MAX(tran_cnt) FROM base) ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear