Rewrite PostgreSQL query using CTE:

user_19650159 0
Viewed 52

I have the following code to pull records from a daterange in PostgreSQL, it works as intended. The "end date" is determined by the "date" column from the last record, and the "start date" is calculated by subtracting a 7-day interval from the "end date".

SELECT date 
FROM files
WHERE daterange((
    (SELECT date FROM files ORDER BY date DESC LIMIT 1) - interval '7 day')::date, -- "start date"
    (SELECT date FROM files ORDER BY date DESC LIMIT 1)::date, -- "end date"
    '(]') @> date::date 
ORDER BY date ASC

I'm trying to rewrite this query using CTEs, so I can replace those subqueries with values such as end_date and start_date. Is this possible using this method or should I look for other alternatives like variables? I'm still learning SQL.

WITH end_date AS 
(
    SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
    SELECT date FROM end_date - INTERVAL '7 day'
) 
SELECT date 
FROM files
WHERE daterange(
    start_date::date, 
    end_date::date, 
    '(]') @> date::date 
ORDER BY date ASC

Right now I'm getting the following error:

ERROR:  syntax error at or near "-"
LINE 7:     SELECT date FROM end_date - INTERVAL '7 day'
postgresql sql
3 Answers

I think this is what you want:

WITH end_date AS 
(
    SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
    SELECT date  - INTERVAL '7 day' as date
    FROM end_date
) 
SELECT F.date, S.date startDate, E.date endDate
FROM files F
JOIN start_date S on F.date >= S.date
JOIN end_date E on F.date <= E.date
ORDER BY date ASC;
user_69961500

You do not need two CTEs, it's one just fine, which can be joined to filter data.

WITH RECURSIVE files AS (
    SELECT CURRENT_DATE date, 1 some_value
    UNION ALL
    SELECT (date + interval '1 day')::date, some_value + 1 FROM files
    WHERE date < (CURRENT_DATE + interval '1 month')::date
),
dates AS (
    SELECT 
        (MAX(date) - interval '7 day')::date from_date,
        MAX(date) to_date 
    FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON daterange(d.from_date, d.to_date, '(]') @> f.date

You even can make it to be a daterange initially in CTE and use it later like this

WITH dates AS (
    SELECT 
        daterange((MAX(date) - interval '7 day')::date, MAX(date), '(]') range
    FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON d.range @> f.date

Here the first CTE is used just to generate some data.

It will get all file lines for dates in the last week, excluding from_date and including to_date.

date some_value
2022-09-26 25
2022-09-27 26
2022-09-28 27
2022-09-29 28
2022-09-30 29
2022-10-01 30
2022-10-02 31
user_58768030

I hope I'm not repeating anything, but if I understand your problem correctly I think this will work:

with cte as (
  select max (date)::date as max_date from files
)
select date
from files
cross join cte
where date >= max_date - 7

Or perhaps even:

select date
from files
where date >= (select max (date)::date - 7 from files)

Since you have already determined that the CTE has the max date, there is really no need to further bound it with a between, <= or range. You can simply say anything after that date minus 7 days.

The error in your code above is because you want this:

SELECT date - INTERVAL '7 day' as date FROM end_date

And not this:

SELECT date FROM end_date - INTERVAL '7 day'

You are subtracting from the table, which doesn't make sense.

user_10018840
Related