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 🙂

Answer

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