Create query returning field containing dates of last 365 days

user_9830755 0
Viewed 275

I'm using AWS QuickSight to build a dashboard with analytics and metrics related to the usage of a system. I'm trying to visualize user's registration over time. I've created a parameter and control on my dashboard that allows the dashboard user to select 'Last N days' (7, 30, 60, 90, 180, 365 days), and I have an associated line chart that will plot the related data.

However the issue is that there are some days where no user's registered, and that leaves gaps of seemingly unreported data (in the line chart). What I would like to do is JOIN my current query on day with a query that returns a single field each row containing the last 365 days.

Select count (DISTINCT id), date_trunc('day', created_at) as day 
FROM users
GROUP BY day
ORDER BY day desc
presto amazon-quicksight sql
1 Answers

To get date instead of numbers you can use below query:

Query:

 with recursive date_range(day,daycount) as
 (
  SELECT '1 Jan 2020'::date as DAY, 1 as daycount
  UNION ALL
  SELECT day+1, daycount+1 from date_range WHERE daycount<365
 )select day from date_range

Output:

 | day        |
 | :--------- |
 | 2020-01-01 |
 | 2020-01-02 |
 | 2020-01-03 |
  .
  .
  .
  .
 | 2020-12-28 |
 | 2020-12-29 |
 | 2020-12-30 |

db<fiddle here

You can use recursive common table expression to generate that. Then you just can join that cte with your table. Please check out below code.

with recursive date_range(day) as
(
 SELECT 1 as day
 UNION ALL
 SELECT day+1 
 from date_range 
 WHERE day < 365
)select DATE_TRUNC('day', NOW() - concat(day,' days')::interval ) as date from date_range

Output:

|date                  |
------------------------
|2021-06-10 00:00:00+01|
|2021-06-09 00:00:00+01|
|2021-06-08 00:00:00+01|
|2021-06-07 00:00:00+01|
|2021-06-06 00:00:00+01|
|2021-06-05 00:00:00+01|
|2021-06-04 00:00:00+01|
|2021-06-03 00:00:00+01|
|2021-06-02 00:00:00+01|
|2021-06-01 00:00:00+01|
...

db<fiddle here

user_86516010
Related