Display the proportion of consumption for each product this month

Keyword

nested subqueries,cost analysis ,Window function,Branch judgment

correlation function

case when,sum,row_number

Aggregate and calculate the total expense of each service based on the service name, use a window function to sort the services by expense in descending order, find six services with the most expense, and then classify the other services as others.Aggregate and calculate the total expense of each service based on the service name, and use a window function to sort the services based on the expense in descending order. Find the six services with the most expense, and classify the rest services as others. The results are displayed in a doughnut chart, which allows you to view the total expense and the proportion of each service.

source :bill |
select
  case
    when rnk > 6
    or pretaxamount <= 0 then 'other'
    else ProductName
  end as ProductName,
  sum(PretaxAmount) as PretaxAmount
from(
    select
      *,
      row_number() over(
        order by
          pretaxamount desc
      ) as rnk
    from(
        select
          ProductName,
          sum(PretaxAmount) as PretaxAmount
        from
          log
        group by
          ProductName
      )
  )
group by
  ProductName
order by
  PretaxAmount desc
limit
  1000