Question 282603 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Selecting Which month of a particular year, contributed most to sales?

I will have to produce a result set which returns the month in each year that produced the highest sales? The tables of interest would be the products table, the order table and the date-time table. The current ERD for this table structure is database structure

Here is my issue what i have tried is creating a common table expression which displays the total sales per month per year for all the years and months stored in the database, which is this

`WITH sales_year_and_month AS 
(SELECT 
       year, 
       month, 
       SUM(orders_table.product_quantity * dim_products.product_price_in_£) AS total_sales 
       from public.dim_date_times
       JOIN public.orders_table on public.orders_table.date_uuid = public.dim_date_times.date_uuid
      JOIN public.dim_products on public.dim_products.product_code = public.orders_table.product_code
      GROUP BY year, month
      ORDER BY total_sales DESC)`

Which returns something along the lines of this sales_year_and_month_ouput

now this table i want to select out the month of a particular year, which contributed most to sales?

I’ve tried multiple ways and still haven’t been able to answer on how to do it properly, i figured it might be an issue of how i fundamentally understand these topics. Would anyone care to share how they would approach this question?

Tags:

2

Answers


  1. 0

    You can use window function like row_number() to pick the month with the highest sale. Here is an example

    WITH sales_year_and_month AS 
(
  SELECT t.*,
         ROW_NUMBER() OVER(PARTITION BY dd.year ORDER BY t.total_sales DESC) AS RN
  FROM (
         SELECT dd.year, 
                dd.month, 
                SUM(O.product_quantity * P.product_price_in_£) AS total_sales 
         from public.dim_date_times dd
         JOIN public.orders_table O
           ON O.date_uuid = dd.date_uuid
         JOIN public.dim_products P
           ON P.product_code = O.product_code
         GROUP BY dd.year, 
                  dd.month) t
)
SELECT year,
       month,
       total_sales
FROM sales_year_and_month
WHERE RN = 1
    Login or Signup to reply.
  2. 0

    I believe you are after a list of year, month and total_sales for the highest sales for each year. Using row_number() over() allows for a convenient way to do this. Fow each partition the numbers start at 1 with the order by DESC determining which row is assigned 1 (the highest sales). Then later we just filter for all the row numbers equal to 1.

    WITH sales_year_and_month
AS (
    SELECT
          *
        , row_number() OVER (PARTITION BY year ORDER BY total_sales DESC) AS rn
    FROM (
        SELECT
              year
            , month
            , SUM(orders_table.product_quantity * dim_products.product_price_in_ £) AS total_sales
        FROM PUBLIC.dim_date_times
        JOIN PUBLIC.orders_table ON PUBLIC.orders_table.date_uuid = PUBLIC.dim_date_times.date_uuid
        JOIN PUBLIC.dim_products ON PUBLIC.dim_products.product_code = PUBLIC.orders_table.product_code
        GROUP BY year
            , month
        )
    )
SELECT
      year
    , month
    , total_sales
FROM sales_year_and_month
WHERE rn = 1
ORDER BY year
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search