CODEWITHSUNDEEP
mysqlsql
Sean Peace
Sean Peace

Reputation: 11

SQL Quarter Total and % increase top ten

I have a table where I need to create total sales by quarter of the product where the account_name and deal_id = X.

I then want a top 10 list of the largest % gainers from the previous quarter.

I have been doing this by creating a temp table, but the size of the table is about 1G and performance is not where we want it to be. We can create another rollup table, but before I do that I would like to see if anyone has a suggestion on using this table first.

account_name    product_title   type    period  deal_id total_amount
Account1    product1    Type A  2002_Q4 9100    146.54
Account1    product1    Type B  2002_Q4 9100    34.32
Account1    product1    Type C  2002_Q4 9100    0.02
Account1    product2    Type A  2002_Q4 9100    14.45
Account1    product2    Type B  2002_Q4 9100    3.58
Account1    product1    Type A  2002_Q3 9100    68.23
Account1    product1    Type B  2002_Q3 9100    12.56
Account1    product1    Type C  2002_Q3 9100    75.21
Account1    product2    Type A  2002_Q3 9100    5.68
Account1    product2    Type B  2002_Q3 9100    3.2

product1    180.88  2002_Q4 16%     
product2    18.03   2002_Q4 103%        
product1    156     2002_Q3         
product2    8.88    2002_Q3

Ok, I added new data and included what the results would look like with the increase listed over the previous quarter.

Upvotes: 0

Views: 77

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Here is one way with fixed quartals:

select d.product_title, ((
        select sum(d1.total_amount)
        from deals d1
        where d1.account_name = 'Account1'
          and d1.deal_id = d.deal_id
          and d1.product_title = d.product_title
          and d1.period  = '2002_Q4'
    ) - sum(d.total_amount)) / sum(d.total_amount) * 100
    as diff
from deals d
where d.account_name = 'Account1'
  and d.deal_id = 9100
  and d.period = '2002_Q3'
group by d.product_title
order by diff desc
limit 10

http://sqlfiddle.com/#!9/65bd95/26

Here is another one - joining two subqueries:

select
  q3.product_title, 
  100 * (q4.total - q3.total) / q3.total as diff
from (
  select d.product_title, sum(d.total_amount) as total
  from deals d
  where d.account_name = 'Account1'
    and d.deal_id = 9100
    and d.period = '2002_Q3'
  group by d.product_title
) q3
join  (
  select d.product_title, sum(d.total_amount) as total
  from deals d
  where d.account_name = 'Account1'
    and d.deal_id = 9100
    and d.period = '2002_Q4'
  group by d.product_title
) q4 using (product_title)
order by diff desc
limit 10

http://sqlfiddle.com/#!9/65bd95/9

A helpfull index would be (account_name, period, deal_id, product_title, total_amount). The first three columns can be in any order. The last one is optional, but makes it to a "covering" index.

Upvotes: 1

Related Questions