CODEWITHSUNDEEP
sqlcommon-table-expressionpsqlpostgresql-9.4
hky404
hky404

Reputation: 1159

storing result of a query based on the date in a rolling monthly table

I am trying to store the result of a query into a monthly table (last 30 days) based on dates.

Example for February 1st 2018: gives me a count of 310

SELECT *
FROM properties p
INNER JOIN orders o
  ON o.property_id = p.id
WHERE o.type = 'Order::PromotedListing'
  AND o.expired_at::DATE > '2018-02-01'
  AND o.created_at::DATE <= '2018-02-01'
ORDER BY o.updated_at

For February 2nd 2018: gives me a count of 307

SELECT *
FROM properties p
INNER JOIN orders o
  ON o.property_id = p.id
WHERE o.type = 'Order::PromotedListing'
  AND o.expired_at::DATE > '2018-02-02'
  AND o.created_at::DATE <= '2018-02-02'
ORDER BY o.updated_at

and so on.

I want to store these counts for last 30 days based on dates in a temp table/CTE. Something like this - 

day, count
2018-02-01, 310
2018-02-02, 307
...
...

so I came up with this query but it is not doing what I am trying to do.

WITH monthly_dates AS (
  SELECT d.date as day
  FROM generate_series(current_date - interval '30 day',
                       current_date,
                       '1 day') AS d
),
featured_listings AS (
  SELECT o.expired_at::date, o.created_at::date, o.updated_at::date
  FROM properties p
  INNER JOIN orders o
    ON o.property_id = p.id
  WHERE o.type = 'Order::PromotedListing'
)
SELECT m.day, COUNT(*)
FROM monthly_dates AS m
LEFT JOIN featured_listings AS f
  ON m.day = f.updated_at
WHERE f.expired_at > m.day
 AND f.created_at <= m.day
GROUP BY 1
ORDER BY 1;

Any input on accomplishing this task will be appreciated.

Upvotes: 1

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You seem to want:

SELECT g.dte, count(o.property_id)
FROM generate_series('2018-02-01'::date, '2018-02-28'::date, interval '1 day'
                    ) g(dte) INNER JOIN
     orders o
     ON o.expired_at::DATE > g.dte AND o.created_at::DATE <= o.gte
GROUP BY g.dte
ORDER BY g.dte;

I don't think you need the properties table for this query.

Upvotes: 1

Related Questions