Count how many orderS daily have free shipping

user_15460560 0
Viewed 72

I really need help in finding how many orders in each day of December 2018 that have this free shipping incentive.

Where in the month of December 2018, we provide customers a marketing incentive. After customers purchase their orders, they get to enjoy free shipping if they purchase another order within the next hour (60 minutes).

For example, if a customer purchases an order at 1.35pm, they will get free shipping if they purchase a second order between 1.35pm and 2.35pm on the same day. From 2.36pm onwards, there will be no free shipping.

Sample Data

Datetime CustomerID Order ID
2020-12-01 03:15 aa771sb 231932
2020-12-01 03:37 aa771sb 231245
2020-12-01 08:12 ab236de 231163
2020-12-02 13:26 ae312qr 231562
2020-12-02 04:52 ae542we 231984

I need to form a table like this.

Day of Purchase All Orders No.Of Orders with free shipping incentive
2018-12-01 xx xx
2018-12-02 xx xx
2018-12-03 xx xx
2018-12-31 xx xx
sql-server sql
2 Answers

You want to know if the first order for the customer is within 1 hour of an order and then summarize by day. This suggests window functions:

select convert(date, datetime),
       count(*) as num_orders
from (select t.*,
             min(datetime) over (partition by customerid) as min_datetime
      from t
     ) t
where datetime >= '2018-12-01' and
      datetime < '2019-01-01' and
      datetime < dateadd(hour, 1, min_datetime) and
      datetime > min_datetime
group by convert(date, datetime)
order by convert(date, datetime);

Note: This assumes that two orders don't have exactly the same date/time value.

user_11440350

You can do this with a combination of LAG and grouping

SELECT
    CAST(Datetime AS date) [Day of Purchase],
    COUNT(*) [All Orders],
    COUNT(CASE WHEN DATEDIFF(second, PrevOrder, Datetime) <= 3600 THEN 1 END) [No.Of Orders with free shipping incentive]
FROM (
    SELECT *,
      LAG(Datetime) OVER (PARTITION BY CustomerID ORDER BY Datetime) PrevOrder
    FROM table
) t
GROUP BY CAST(Datetime AS date)

Note that this still counts an order as free shipping if the other order was less than an hour ago, but on the previous day. For example 23:45 and 00:20.

user_148689970
Related