SQLize Online / PHPize Online  /  SQLtest Online

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'); SELECT t.user_id, t.transaction_date, v.user_id AS visit_user_id, v.visit_date FROM Transactions t LEFT JOIN Visits v ON t.user_id = v.user_id AND t.transaction_date= v.visit_date UNION ALL SELECT t.user_id, t.transaction_date, v.user_id AS visit_user_id, v.visit_date FROM Transactions t RIGHT JOIN Visits v ON t.user_id = v.user_id AND t.transaction_date= v.visit_date ; SELECT 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(v.visit_date) OVER (PARTITION BY v.user_id, v.visit_date) AS visits_count FROM Transactions t FULL JOIN Visits v ON t.user_id = v.user_id AND t.transaction_date= v.visit_date ORDER BY transactions_count ;