Selecting the customer who made the most purchases, grouped by date

user_10981808 0
Viewed 101

I have two tables

customers
+---------+-------+
| cust_id | name  |
+---------+-------+
| 1       | Tom   |
+---------+-------+
| 2       | John  |
+---------+-------+
| 3       | Lisa  |
+---------+-------+
| 4       | Wendy |
+---------+-------+

purchases
+---------------+-------------+---------+
| purchase_date | purchase_id | cust_id |
+---------------+-------------+---------+
| 2021-01-01    | 1           | 1       |
+---------------+-------------+---------+
| 2021-01-01    | 2           | 1       |
+---------------+-------------+---------+
| 2021-01-01    | 3           | 2       |
+---------------+-------------+---------+
| 2021-01-01    | 4           | 1       |
+---------------+-------------+---------+
| 2021-01-01    | 5           | 4       |
+---------------+-------------+---------+
| 2021-01-02    | 6           | 3       |
+---------------+-------------+---------+
| 2021-01-02    | 7           | 3       |
+---------------+-------------+---------+
| 2021-01-02    | 8           | 2       |
+---------------+-------------+---------+
| 2021-01-02    | 9           | 1       |
+---------------+-------------+---------+
| 2021-01-02    | 10          | 4       |
+---------------+-------------+---------+
| 2021-01-03    | 11          | 2       |
+---------------+-------------+---------+
| 2021-01-03    | 12          | 2       |
+---------------+-------------+---------+
| 2021-01-03    | 13          | 3       |
+---------------+-------------+---------+
| 2021-01-03    | 14          | 3       |
+---------------+-------------+---------+

I want to query the count of unique purchasing customers by date (easy) and the cust_id of the customer who made the most purchases by date. If more than one customer made the same number of purchases on the same date, I want to show the lesser cust_id. The results should look like this:

+---------------+------------------+-----------------+
| purchase_date | unique_customers | biggest_spender |
+---------------+------------------+-----------------+
| 2021-01-01    | 3                | 1               |
+---------------+------------------+-----------------+
| 2021-01-02    | 4                | 3               |
+---------------+------------------+-----------------+
| 2021-01-03    | 2                | 2               |
+---------------+------------------+-----------------+
max group-by postgresql sql
1 Answers

Here is the query in Postgresql, using mode() to determine the biggest spender, alias the most frequent value for each date in your purchase table

SELECT p.purchase_date, count(DISTINCT p.cust_id) as unique_customers , mode() within group (order by p.cust_id) as biggest_spender 
FROM purchases p
GROUP BY p.purchase_date
ORDER BY COUNT(p.cust_id) DESC;
user_156907130
Related