SQLize Online / PHPize Online  /  SQLtest Online

Create table Users (user_id int, join_date date, favorite_brand varchar(10)); Create table Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int); Create table Items (item_id int, item_brand varchar(10)); insert into Users (user_id, join_date, favorite_brand) values ('1', TO_DATE('2018-01-01', 'yyyy-mm-dd'), 'Lenovo'); insert into Users (user_id, join_date, favorite_brand) values ('2', TO_DATE('2018-02-09', 'yyyy-mm-dd'), 'Samsung'); insert into Users (user_id, join_date, favorite_brand) values ('3', TO_DATE('2018-01-19', 'yyyy-mm-dd'), 'LG'); insert into Users (user_id, join_date, favorite_brand) values ('4', TO_DATE('2018-05-21', 'yyyy-mm-dd'), 'HP'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', TO_DATE('2019-08-01', 'yyyy-mm-dd'), '4', '1', '2'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', TO_DATE('2018-08-02', 'yyyy-mm-dd'), '2', '1', '3'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', TO_DATE('2019-08-03', 'yyyy-mm-dd'), '3', '2', '3'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', TO_DATE('2018-08-04', 'yyyy-mm-dd'), '1', '4', '2'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', TO_DATE('2018-08-04', 'yyyy-mm-dd'), '1', '3', '4'); insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', TO_DATE('2019-08-05', 'yyyy-mm-dd'), '2', '2', '4'); insert into Items (item_id, item_brand) values ('1', 'Samsung'); insert into Items (item_id, item_brand) values ('2', 'Lenovo'); insert into Items (item_id, item_brand) values ('3', 'LG'); insert into Items (item_id, item_brand) values ('4', 'HP'); -- buyer_id | join_date | orders_in_2019 --WHERE EXTRACT(YEAR FROM o.order_date) = 2019 SELECT u.user_id as buyer_id, u.join_date, COUNT(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2019 THEN o.order_id ELSE NULL END) as orders_in_2019 FROM Users u LEFT JOIN Orders o ON u.user_id = o.buyer_id GROUP BY u.user_id, u.join_date ; SELECT u.user_id as buyer_id, TO_CHAR(u.join_date, 'yyyy-mm-dd') AS join_date, COUNT(o.order_id) as orders_in_2019 FROM Users u LEFT JOIN ( SELECT order_id, buyer_id FROM Orders WHERE EXTRACT(YEAR FROM order_date) = 2019 ) o ON u.user_id = o.buyer_id GROUP BY u.user_id, u.join_date ;