Case Study in SQL

Question

You have a table with the following 3 columns

customer_id, order_id, order_date
123.                   987.            2/9/2020
123                    1234            4/9/2020
456                    1211             1/1/2019
456                   2344            8/8/2020

We want to know the number of customer who have their first two orders in the last 180 days. In the above example 123 will qualify but 456 won’t qualify

in progress 2
TheDataMonk 55 years 3 Answers 1345 views Grand Master 1

Answers ( 3 )

    -3
    Reply

    select customer_id, min(order_date) as first_order
    from customer
    group by customer_id
    having datediff(curdate(),min(order_date)<=180

  2. Harshit Goyal
    1
    Reply

    SELECT count(customer_id) FROM
    (SELECT customer_id, TotalRows FROM
    (SELECT customer_id, COUNT(order_id) As totalRows
    FROM table
    GROUP BY customer_id
    HAVING MIN(order_date) >= DATEADD(day, -180, GetDate())) As t1
    WHERE TotalRows >= 2) As t2

  3. aaronjosephmathew
    4
    Reply

    select count(DISTINCT(customer_id))from
    (
    — To obtain the order id
    select *, ROW_NUMBER() over (PARTITION BY customer_id order by order_date asc) as order_number from trx_table)
    as a
    where a.order_date >= DATE_SUB(NOW(), INTERVAL 180 DAYS and a.order_number in (1,2)

Leave an answer

Browse
Browse