I can get a list of each day of last 30 days row by row with this query and it works.. But I don't think that it is the best one since the "WHERE" part is messed up. I draw a graph and I just want to get daily_total_invoice_amount for sum of each day of last 30 days.

  sum(b.invoice_amount) daily_total_invoice_amount,
  extract(day from a.order_creation_date)   as day,
  extract(month from a.order_creation_date) as month
FROM orders a
  INNER JOIN order_items b
    ON a.order_id = b.order_id
  order_creation_date BETWEEN (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '2 DAY') 
    GROUP BY month, day
    order by month DESC, day DESC
  • 3
    You could simply your WHERE clause to between current_date - 30 and current_date + 1 – a_horse_with_no_name Apr 5 '18 at 19:56
  • Have you considered generate_series? – Vérace Apr 5 '18 at 20:05
  • @Vérace Honestly I don't have any idea. I will check it out. It would be great if you could have shown how to do it with it. It seems like ( I googled ) it is done for this kind of queries. – SNaRe Apr 5 '18 at 20:10
  • 1
    Have a look here and here. – Vérace Apr 5 '18 at 20:18
  • Is your column orders.order_creation_date data type date as the name indicates? Can there be days without any orders and do you still want a row for such days in the result? All your questions should include your version of Postgres and table definitions (CREATE TABLE statements) showing data types and constraints. – Erwin Brandstetter Apr 6 '18 at 3:00

Why include tomorrow (CURRENT_DATE + INTERVAL '1 DAY')? A future date in order_creation_date would normally indicate a data error. I would not even include today in the statistic, since that is typically incomplete and misleading until the day is over. Consequently I use the last 30 days up until yesterday in my query.

Assuming order_creation_date is an actual date, just GROUP BY and ORDER BY that date, that's simpler an cheaper than using extracted month and day numbers for the same. If it's not a date, but a timestamp or timestamptz, just cast to date to group by the day: o.order_creation_date::date. Be aware that the result depends on the timezone setting of the session when dealing with timestamptz (Day boundaries are defined by the time zone.)

Also assuming you want a result row for every single one of the 30 days. To make sure, days without orders don't go missing, generate the series of days with generate_series() and LEFT JOIN to that. You can generate timestamps, but I use the simpler integer variant while we are operating with dates. We can just subtract an integer from a date to subtract days.

     , sum(oi.invoice_amount) AS daily_total_invoice_amount
FROM   generate_series (1, 30) i -- to return exactly 30 rows
LEFT   JOIN orders      o ON o.order_creation_date = CURRENT_DATE - i
LEFT   JOIN order_items oi USING (order_id)
GROUP  BY i    -- effectively group by days
ORDER  BY i;   -- effectively descending dates
  • I include today because I would like to show today's total amount in the chart as well, it is a product need, otherwise I agree with you. It is just a preference, as we can get current day's stats in analytics with additional option. I run this query but I got this [42712] ERROR: table name "i" specified more than once Details – SNaRe Apr 6 '18 at 6:38
  • I solved it by changing i to any letter ( x ) LEFT JOIN order_items i(x) USING (order_id) and sum(i.invoice_amount) i(x ). But the result ( daily_total_invoice_amount ) that I get is not same as I get from regular query. They are mostly null ( all of my field has value no 0 ) and daily_total_invoice_amount is pretty low. I will try to understand the whole query to make it work. I am sure this is the right way. – SNaRe Apr 6 '18 at 6:44
  • 1
    Sorry about the duplicate table alias. Fixed. There are often several good ways with SQL. I am just proposing one. The best solution depends on the actual environment and exact requirements. You didn't disclose a whole lot of information - starting with the table definition. My solution is based on data type date, as stated. – Erwin Brandstetter Apr 6 '18 at 15:36

Here is my workaround, base on Vérace Apr 5 at 20:18 first referenced article, the performance is not optimal, but it works.

select date(d) ds
, count(distinct case when c.ds = date(d) then c.user_id else null end) d0_user_count
, count(distinct case when c.ds <= date(d) and c.ds >= date(d) - 3 then c.user_id else null end) d3_user_count
, count(distinct c.user_id) d5_user_count
from generate_series(
  current_date - interval '30 day', 
  '1 day'
) d
left join user_click c
on c.ds <= date(d) and c.ds >= date(d) - 5
group by date(d)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.