CODEWITHSUNDEEP
sqlpostgresql
wwchang
wwchang

Reputation: 55

Selecting monthly peak sales with SQL query

I'm experimenting with the Adventureworks database in psql, and I wanted to try and select peak monthly sales over the years available in the database. So far, my query is the following:

WITH ranked_monthly_sales AS (
SELECT
    RANK() OVER (PARTITION BY 
        to_char(soh.orderdate, 'MM') 
        ORDER BY SUM(sod.unitprice) DESC
        ) AS rank,
    to_char(soh.orderdate, 'MM') AS order_month,
    to_char(soh.orderdate, 'YYYY') AS order_year,
    SUM(sod.unitprice) AS unit_price
FROM
    sales.salesorderheader soh
INNER JOIN sales.salesorderdetail sod
    ON soh.salesorderid = sod.salesorderid
GROUP BY
    order_month,
    order_year
ORDER BY
    order_year,
    order_month
)
SELECT *
FROM ranked_monthly_sales
WHERE rank = 1
ORDER BY rank, order_year, order_month, order_year;

This returns the following result:

rank    order_month     order_year  unit_price
1       6               2013        2148488.3562
1       7               2013        2139025.3667
1       8               2013        1736638.0688
1       9               2013        2197327.4224
1       10              2013        2534203.6808
1       11              2013        2245166.9681
1       12              2013        2367052.5368
1       1               2014        2607573.234
1       2               2014        1336516.3606
1       3               2014        3489718.7278
1       4               2014        1796448.3395
1       5               2014        3172559.0484

However, I'm trying to get something akin to what I created in Tableau, using this calculated field:

IF
    SUM([unit_price]) > LOOKUP(SUM([unit_price]),-1)
    AND
    SUM([unit_price]) > LOOKUP(SUM([unit_price]),1)
THEN
    'Peak'
END

Using this field as a filter gives me wildly different results, as shown in this table:

Month of order_date     Peaks   unit_price
December 2012           Peak    1,257,847.1714
August 2011             Peak    1,263,165.3553
September 2012          Peak    1,367,601.4629
March 2013              Peak    1,454,903.3623
March 2012              Peak    1,553,199.7152
June 2012               Peak    1,602,987.706
January 2012            Peak    1,955,076.8224
October 2011            Peak    1,983,602.0312
June 2013               Peak    2,148,488.3562
October 2013            Peak    2,534,203.6808
January 2014            Peak    2,607,573.234
May 2014                Peak    3,172,559.0484
March 2014              Peak    3,489,718.7278

Can someone help me fix my query to get what I am looking for?

Much appreciated.

Upvotes: 1

Views: 150

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

If you want monthly sales that are larger than the previous month or the next month, then use lead()/lag():

WITH monthly_sales AS (
      SELECT to_char(soh.orderdate, 'YYYY-MM') AS yyyymm,
             SUM(sod.unitprice) AS sales
      FROM sales.salesorderheader soh INNER JOIN
           sales.salesorderdetail sod
           ON soh.salesorderid = sod.salesorderid
      GROUP BY to_char(soh.orderdate, 'YYYY-MM')
     )
SELECT *
FROM (SELECT ms.*,
             LAG(sales) OVER (ORDER BY yyyymm) as prev_sales,
             LEAD(sales) OVER (ORDER BY yyyymm) as next_sales,
      FROM monthly_sales ms
     ) ms
WHERE sales > prev_sales AND sales > next_sales

Upvotes: 1

Related Questions