SQL Join: have all values from both sides with an accumulative condition (Presto/AWS Athena)

user_13511610 0
Viewed 112

I've been looking at this seemingly simple problem for a while with no solution, assume I have a table with a list of dates, and another table with phone numbers and people and dates, I need to have a final result which has all names and all dates, with a third column that has the number of unique phone numbers appeared in any date that is the same or larger than the date in the result, this is an example:

t1
+------------+
|    date    |
+------------+
| 01/01/2020 |
| 01/02/2020 |
| 01/03/2020 |
| 01/04/2020 |
| 01/05/2020 |
| 01/06/2020 |
| 01/07/2020 |
| 01/08/2020 |
+------------+

t2
+------+------------+--------------+
| name |    date    | phone_number |
+------+------------+--------------+
| John | 01/01/2020 |          123 |
| Mike | 01/02/2020 |          456 |
| Mike | 01/03/2020 |          789 |
| John | 01/04/2020 |          999 |
| Mike | 01/05/2020 |          111 |
| John | 01/06/2020 |          777 |
| Mike | 01/07/2020 |          123 |
| Mike | 01/08/2020 |          456 |
| John | 01/01/2020 |          789 |
| John | 01/02/2020 |          789 |
| Mike | 01/03/2020 |          789 |
| John | 01/04/2020 |          789 |
+------+------------+--------------+

The result I am aiming for:

+------+------------+-----------------------------------------------------------------+
| Name |   Month    | Comulative Unique Numbers (Unique Numbers in any date >= Month) |
+------+------------+-----------------------------------------------------------------+
| John | 01/01/2020 |                                                               4 |
| John | 01/02/2020 |                                                               3 |
| John | 01/03/2020 |                                                               3 |
| John | 01/04/2020 |                                                               3 |
| John | 01/05/2020 |                                                               1 |
| John | 01/06/2020 |                                                               1 |
| John | 01/07/2020 |                                                               0 |
| John | 01/08/2020 |                                                               0 |
| Mike | 01/01/2020 |                                                               4 |
| Mike | 01/02/2020 |                                                               4 |
| Mike | 01/03/2020 |                                                               4 |
| Mike | 01/04/2020 |                                                               3 |
| Mike | 01/05/2020 |                                                               3 |
| Mike | 01/06/2020 |                                                               2 |
| Mike | 01/07/2020 |                                                               2 |
| Mike | 01/08/2020 |                                                               1 |
+------+------------+-----------------------------------------------------------------+

I tried so many ways, and this is what I thought the closest:

SELECT * FROM t1
LEFT OUTER JOIN
(SELECT t1.date, COUNT(DISTINCT phone_number) count, name FROM t1
LEFT OUTER JOIN
t2
ON t1.date < t2.date
GROUP BY t1.date,t2.name
ORDER BY 2 DESC) temp
ON t1.date = temp.date

I still get missing rows from the final result.

This is what I am getting:

+------+------------+-------+
| name |    date    | count |
+------+------------+-------+
| null | 2020-08-01 |     0 |
| John | 2020-01-01 |     3 |
| John | 2020-02-01 |     3 |
| John | 2020-03-01 |     3 |
| John | 2020-04-01 |     1 |
| John | 2020-05-01 |     1 |
| Mike | 2020-01-01 |     4 |
| Mike | 2020-02-01 |     4 |
| Mike | 2020-03-01 |     3 |
| Mike | 2020-04-01 |     3 |
| Mike | 2020-05-01 |     2 |
| Mike | 2020-06-01 |     2 |
| Mike | 2020-07-01 |     1 |
+------+------------+-------+
presto amazon-athena sql
1 Answers

Using a calendar table approach, we can build a reference table consisting of all names along with all dates. Then, left join this to your second table which contains the actual data:

SELECT
    b.name,
    a.date,
    COUNT(DISTINCT t.phone_number) AS unique_numbers
FROM t1 a
CROSS JOIN (SELECT DISTINCT name FROM t2) b
LEFT JOIN t2 t
    ON a.date = t.date AND b.name = t.name
GROUP BY
    b.name,
    a.date
ORDER BY
    b.name,
    a.date;
user_18632290
Related