none
calculte metrics in MS SQL SERVER 2008 RRS feed

  • Question

  • 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

    Tuesday, October 27, 2020 11:31 AM

Answers