CODEWITHSUNDEEP
sqldategroup-bywindow-functions
Niklas9
Niklas9

Reputation: 9386

Getting date, and count of unique customers when first order was placed

I have a table called orders that looks like this:

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| id           | int(11) | YES  |     | NULL    |       |
| memberid     | int(11) | YES  |     | NULL    |       |
| deliverydate | date    | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+

And that contains the following data:

+------+----------+--------------+
| id   | memberid | deliverydate |
+------+----------+--------------+
|    1 |      991 | 2019-10-25   |
|    2 |      991 | 2019-10-26   |
|    3 |      992 | 2019-10-25   |
|    4 |      992 | 2019-10-25   |
|    5 |      993 | 2019-10-24   |
|    7 |      994 | 2019-10-21   |
|    6 |      994 | 2019-10-26   |
|    8 |      995 | 2019-10-26   |
+------+----------+--------------+

I would like a result set returning each unique date, and a separate column showing how many customers that placed their first order that day.

I'm having problems with querying this the right way, especially when the data consists of multiple orders the same day from the same customer.

My approach has been to

  1. Get all unique memberids that placed an order during the time period I want to look at
  2. Filter out the ones that placed their first order during the period by comparing the memberids that has placed an order before the timeperiod
  3. Grouping by delivery date, and counting all unique memberids (but this obviously counts unique memberids each day individually!)

Here's the corresponding SQL:

SELECT deliverydate,COUNT(DISTINCT memberid) FROM orders
WHERE 
    MemberId IN (SELECT DISTINCT memberid FROM orders WHERE deliverydate BETWEEN '2019-10-25' AND '2019-10-26')
AND NOT 
    MemberId In (SELECT DISTINCT memberid FROM orders WHERE deliverydate < '2019-10-25')
GROUP BY deliverydate
ORDER BY deliverydate ASC;

But this results in the following with the above data:

+--------------+--------------------------+
| deliverydate | COUNT(DISTINCT memberid) |
+--------------+--------------------------+
| 2019-10-25   |                        2 |
| 2019-10-26   |                        2 |
+--------------+--------------------------+

The count for 2019-10-26 should be 1.

Appreciate any help :)

Upvotes: 0

Views: 912

Answers (3)

forpas
forpas

Reputation: 164099

Get the first order of each customer with NOT EXISTS and then GROUP BY deliverydate to count the distinct customers who placed their order: 

select o.deliverydate, count(distinct o.memberid) counter
from orders o
where not exists (
  select 1 from orders
  where memberid = o.memberid and deliverydate < o.deliverydate
)  
group by o.deliverydate

See the demo.
Results:

| deliverydate        | counter |
| ------------------- | ------- |
| 2019-10-21 00:00:00 | 1       |
| 2019-10-24 00:00:00 | 1       |
| 2019-10-25 00:00:00 | 2       |
| 2019-10-26 00:00:00 | 1       |

But if you want results for all the dates in the table including those dates where there where no orders from new customers (so the counter will be 0):

select d.deliverydate, count(distinct o.memberid) counter
from (
  select distinct deliverydate  
  from orders
) d left join orders o
on o.deliverydate = d.deliverydate and not exists (
  select 1 from orders
  where memberid = o.memberid and deliverydate < o.deliverydate
)  
group by d.deliverydate

Upvotes: 0

Mike
Mike

Reputation: 328

you have first to figure out when was the first delivery date:

SELECT firstdeliverydate,COUNT(DISTINCT memberid) FROM (
select memberid, min(deliverydate) as firstdeliverydate
from orders
WHERE 
    MemberId IN (SELECT DISTINCT memberid FROM orders WHERE deliverydate BETWEEN '2019-10-25' AND '2019-10-26')
AND NOT 
    MemberId In (SELECT DISTINCT memberid FROM orders WHERE deliverydate < '2019-10-25')
group by memberid)
t1
group by firstdeliverydate

Upvotes: 0

GMB
GMB

Reputation: 222482

You can aggregate twice:

select first_deliverydate, count(*) cnt
from (
    select min(deliverydate) first_deliverydate
    from orders
    group by memberid
) t
group by first_deliverydate
order by first_deliverydate

The subquery gives you the first order data of each member, then the outer query aggregates and counts by first order date.

This demo on DB Fiddle with your sample data returns:

first_deliverydate | cnt
:----------------- | --:
2019-10-21         |   1
2019-10-24         |   1
2019-10-25         |   2
2019-10-26         |   1

In MySQL 8.0, This can also be achieved with window functions:

select deliverydate first_deliverydate, count(*) cnt
from (
    select deliverydate, row_number() over(partition by memberid order by deliverydate) rn
    from orders
) t
where rn = 1
group by deliverydate
order by deliverydate

Demo on DB Fiddle

Upvotes: 2

Related Questions