Today vs weeks ago with aggregate function

Question

I'm working on the following presto/sql query using inline filter to get side by side comparison of current date range vs weeks ago data.

In my case query current date range is 2017-09-13 to 2017-09-14.

So far I'm able to get the following results, but unfortunately this is not what I want.

Any kind of help would be greatly appreciated.

 SELECT
        DATE_TRUNC('day',DATE_PARSE(CAST(sample.datep AS VARCHAR),'%Y%m%d')) AS date,
        CAST(SUM(sample.page_views) FILTER (WHERE sample.datep BETWEEN 20170913 AND 20170914) AS DOUBLE) AS page_views,
        CAST(SUM(sample.page_views) FILTER (WHERE sample.datep BETWEEN 20170906 AND 20170907) AS DOUBLE) AS page_views_weeks_ago 
    FROM
        sample 
    WHERE
        (
            datep BETWEEN 20170906 AND 20170914
        ) 
    GROUP BY
        1 
    ORDER BY
        1 ASC 
    LIMIT 50

Actual result:

+------------+------------+----------------------+
|    date    | page_views | page_views_weeks_ago |
+------------+------------+----------------------+
| 2017-09-06 |          0 |              990,929 |
| 2017-09-07 |          0 |              913,802 |
| 2017-09-08 |          0 |                    0 |
| 2017-09-09 |          0 |                    0 |
| 2017-09-10 |          0 |                    0 |
| 2017-09-11 |          0 |                    0 |
| 2017-09-12 |          0 |                    0 |
| 2017-09-13 |  1,507,715 |                    0 |
| 2017-09-14 |     48,625 |                    0 |
+------------+------------+----------------------+

Expected result:

+------------+------------+----------------------+
|    date    | page_views | page_views_weeks_ago |
+------------+------------+----------------------+
| 2017-09-13 |  1,507,715 |              990,929 |
| 2017-09-14 |     48,625 |              913,802 |
+------------+------------+----------------------+

Answer 1

You can achieve with joining a table with itself as a previous day. For brevity, I assume that we have a date field so that date substructions can be done easily.

SELECT date,
       SUM(curr.page_views) AS page_views,
       SUM(prev.page_views) AS page_views_weeks_ago 
FROM sample curr
JOIN sample prev ON curr.date - 7 = prev.date
GROUP BY 1 
ORDER BY 1 ASC