I have to find for each user, the join date and the number of orders they made as a buyer in 2019.
Table: Users
---------------- ---------
| Column Name | Type |
---------------- ---------
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
---------------- ---------
user_id is the primary key of this table. This table has the info of the users of an online shopping website where users can sell and buy items.
Table: Orders
--------------- ---------
| Column Name | Type |
--------------- ---------
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
--------------- ---------
order_id is the primary key of this table. item_id is a foreign key to the Items table. buyer_id and seller_id are foreign keys to the Users table.
Table: Items
--------------- ---------
| Column Name | Type |
--------------- ---------
| item_id | int |
| item_brand | varchar |
--------------- ---------
item_id is the primary key of this table.
Input:
Users table:
--------- ------------ ----------------
| user_id | join_date | favorite_brand |
--------- ------------ ----------------
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
| 4 | 2018-05-21 | HP |
--------- ------------ ----------------
Orders table:
---------- ------------ --------- ---------- -----------
| order_id | order_date | item_id | buyer_id | seller_id |
---------- ------------ --------- ---------- -----------
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2018-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2018-08-04 | 1 | 4 | 2 |
| 5 | 2018-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
---------- ------------ --------- ---------- -----------
Items table:
--------- ------------
| item_id | item_brand |
--------- ------------
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
--------- ------------
Output:
----------- ------------ ----------------
| buyer_id | join_date | orders_in_2019 |
----------- ------------ ----------------
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
----------- ------------ ----------------
I wrote the following solution but it doesn't work. Kindly explain what is wrong with it.
select u.user_id as buyer_id, u.join_date as join_date, count(o.order_id) as orders_in_2019
from Users u join Orders o on u.user_id = o.buyer_id
where o.order_date < date('2010-01-01') and o.order_date >= date('2019-01-01')
group by u.user_id;
This gives me empty result.
CodePudding user response：
Apart from the date typo(o.order_date < '2010-01-01') you appear to want all users even if they have no orders in 2019 so left join and conditional aggregation seems appropriate
select u.user_id as buyer_id, u.join_date as join_date
,sum(case when o.order_date < '2020-01-01' and o.order_date >= '2019-01-01' then 1 else 0 end) cnt
from users u
left join Orders o on u.user_id = o.buyer_id
group by u.user_id ;
---------- ------------ ------
| buyer_id | join_date | cnt |
---------- ------------ ------
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
---------- ------------ ------
4 rows in set (0.001 sec)
CodePudding user response：
You can not select a direct element that is not in the group by close
select g_user.buyer_id, Users.join_date as join_date , g_user.orders_in_2019 From (select u.user_id as buyer_id, count(o.order_id) as orders_in_2019 From Users u
join Orders o on u.user_id = o.buyer_id
where o.order_date < date('2010-01-01') and o.order_date >= date('2019-01-01')
group by u.user_id)
as g_user join Users on g_user.buyer_id = Users.user_id;
CodePudding user response：
This is because your date is not correct in sql:
o.order_date < date('2010-01-01')
It is obvious none of your order is before 2010. You should change to
o.order_date < date('2020-01-01')