Query for count of distinct values in a rolling date range

by

Test case:

CREATE TABLE tbl (date date, email text);
INSERT INTO tbl VALUES
  ('2012-01-01', '[email protected]')
, ('2012-01-01', '[email protected]')
, ('2012-01-01', '[email protected]')
, ('2012-01-02', '[email protected]')
, ('2012-01-02', '[email protected]')
, ('2012-01-03', '[email protected]')
, ('2012-01-04', '[email protected]')
, ('2012-01-05', '[email protected]')
, ('2012-01-05', '[email protected]')
, ('2012-01-06', '[email protected]')
, ('2012-01-06', '[email protected]')
, ('2012-01-06', '[email protected]`')
;

Query – returns only days where an entry exists in tbl:

SELECT date
     ,(SELECT count(DISTINCT email)
       FROM   tbl
       WHERE  date BETWEEN t.date - 2 AND t.date -- period of 3 days
      ) AS dist_emails
FROM   tbl t
WHERE  date BETWEEN '2012-01-01' AND '2012-01-06'  
GROUP  BY 1
ORDER  BY 1;

Or – return all days in the specified range, even if there are no rows for the day:

SELECT date
     ,(SELECT count(DISTINCT email)
       FROM   tbl
       WHERE  date BETWEEN g.date - 2 AND g.date
      ) AS dist_emails
FROM  (SELECT generate_series(timestamp '2012-01-01'
                            , timestamp '2012-01-06'
                            , interval  '1 day')::date) AS g(date);

db<>fiddle here

Result:

day        | dist_emails
-----------+------------
2012-01-01 | 3
2012-01-02 | 3
2012-01-03 | 3
2012-01-04 | 3
2012-01-05 | 1
2012-01-06 | 2

This sounded like a job for window functions at first, but I did not find a way to define the suitable window frame. Also, per documentation:

Aggregate window functions, unlike normal aggregate functions, do not
allow DISTINCT or ORDER BY to be used within the function argument list.

So I solved it with correlated subqueries instead. I guess that’s the smartest way.

BTW, “between said date and 3 days ago” would be a period of 4 days. Your definition is contradictory there.

Slightly shorter, but slower for few days:

SELECT g.date, count(DISTINCT email) AS dist_emails
FROM  (SELECT generate_series(timestamp '2012-01-01'
                            , timestamp '2012-01-06'
                            , interval  '1 day')::date) AS g(date)
LEFT   JOIN tbl t ON t.date BETWEEN g.date - 2 AND g.date
GROUP  BY 1
ORDER  BY 1;

Related:

More Related Contents:

  1. Fast way to discover the row count of a table in PostgreSQL
  2. Query with LEFT JOIN not returning rows for count of 0
  3. How do you do date math that ignores the year?
  4. Compute percents from SUM() in the same SELECT sql query
  5. Getting date list in a range in PostgreSQL
  6. PostgreSQL: how to convert from Unix epoch to date?
  7. Conditional SQL count
  8. Check whether string is a date Postgresql
  9. How do I speed up counting rows in a PostgreSQL table?
  10. How to compare dates in datetime fields in Postgresql?
  11. Run a query with a LIMIT/OFFSET and also get the total number of rows
  12. Postgresql query between date ranges
  13. Getting results between two dates in PostgreSQL
  14. ORDER BY the IN value list
  15. How to use count and group by at the same select statement
  16. How to update selected rows with values from a CSV file in Postgres?
  17. Update multiple rows in same query using PostgreSQL
  18. How do I determine the last day of the previous month using PostgreSQL?
  19. PL/pgSQL checking if a row exists
  20. Find closest date in SQL Server
  21. Get the distinct sum of a joined table column
  22. SQL count(*) performance
  23. How much disk-space is needed to store a NULL value using postgresql DB?
  24. PostgreSQL convert columns to rows? Transpose?
  25. Regular expression in PostgreSQL LIKE clause
  26. Postgres Error: More than one row returned by a subquery used as an expression
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. Subtract hours from the now() function
  29. Date serial in SQL?
  30. Get most common value for each value of another column in SQL

Leave a Comment