Hi, can you please help me calculate any metrics needed to me , for my data analysis
example of sample
create table comm
(
order_id int,
created datetime,
user_id int
)
insert into comm values
(1,27.09.2020,1),
(2,27.09.2020,1),
(3,27.09.2020,1),
(4,27.10.2020,1)
(5,27.10.2020,1),
(6,27.10.2020,1),
(7,27.10.2020,0);
select * from comm
For each user_id and each date of order's creation (created) calculate number of orders.
For example for user_id and date 27.09.2020 was 3 orders. And for date 27.10.2020 also was 3 ordes of user_id=1
and 0 orders for user_id=0
date user_id count orders
27.09.2020 1 3
27.10.2020 1 3
27.10.2020 0 0
Also i need the same but only for the last date of the month
for example only two rows
date user_id count orders
27.10.2020 1 3
27.10.2020 0 0
and last metric
Get those user_id who have had at least one order every day in the last month.
so output
user_id
1
how can i calculate such metrics?
Thank you
