Display yesterday's consumption and year-on-year comparison with last month

Keyword

nested subqueries,General aggregation analysis,cost analysis

correlation function

sum,coalesce,round,compare

The results are displayed in a month-to-month comparison chart, which shows the total consumption of yesterday and the comparison to the same day in the last month.

source :bill |
select
  round(diff [1], 2),
  round(diff [2], 2),
  round(diff [3] * 100 -100, 2)
from(
    select
      compare("Yesterday's consumption", 604800) as diff
    from(
        select
          round(coalesce(sum(PretaxAmount), 0), 3) as "Yesterday's consumption"
        from
          log
      )
  )