Analyzing Sales Using Window Frames and Window Functions

Hi there!

I’m Jacques and I’m taking the ‘SQL for Data Analyitcs’ course !

I’m struggling with the windows functions part. I don’t know if everyone has already done it?
I’m trying to calculate the 30-days rolling average for the daily number of sales deals. I’m pretty sure that my request is right, but apparently not haha! Please can someone have a look on my code? Thanks so much by advance!!! :slight_smile:

with daily_sales as (

  select sales_transaction_date::date 
  , count(*) as total_sales_count
  from sales
  where extract(year from sales_transaction_date::date) = 2018
  group by 1 
  order by 1

),  rolling_average as (

    select sales_transaction_date 
    , total_sales_count
    , avg(total_sales_count) over(order by sales_transaction_date rows between 30 preceding and current row) as deals_moving_average_30
    , row_number() over(order by sales_transaction_date) as rank
    from daily_sales
    group by 1, 2 
    order by 1

) select sales_transaction_date
, case when rank >= 30 then deals_moving_average_30 else null end as deals_moving_average_30 
from rolling_average
order by 1;

Jacques

Hey Jacques,

Your answer is very close! It looks like the only thing you are missing is the filter to use only 2018 sales. I’ve included the solution below. Let me know if you have any questions!

Solution
WITH daily_deals AS (
    SELECT
        sales_transaction_date::DATE,
        COUNT(*) AS total_deals
    FROM
        sales
    GROUP BY 1
),
moving_average_calculation_30 AS (
    SELECT
        sales_transaction_date,
        total_deals,
        AVG(total_deals) OVER (
            ORDER BY sales_transaction_date ROWS
            BETWEEN 30 PRECEDING and CURRENT ROW
        ) AS deals_moving_average,
        ROW_NUMBER() OVER (
            ORDER BY sales_transaction_date) AS row_number
    FROM
        daily_deals
    ORDER BY 1
)
SELECT
    sales_transaction_date,
    CASE
        WHEN row_number >= 30 THEN deals_moving_average
        ELSE NULL
    END AS deals_moving_average_30
FROM
    moving_average_calculation_30
WHERE
    sales_transaction_date >= '2018-01-01'
    AND sales_transaction_date < '2019-01-01';

Oh alright, I got it! I made the filter at the beginning so I could have a table with all the informations that I need to work. But in that case, I didn’t take all the data before 2018, which have made the average wrong because it would start from 2018, and not before. We just need to select all data from 2018, with their average calculated from 2010… I think I got it, thank you so much!

I have another question, about the next request of the same excercice, maybe I’m making the same type of mistake ? Here is my code:

    with daily_sales as (
      select dealership_id
      , sum(sales_amount) as total_sales
      from sales 
      where channel = 'dealership'
      group by 1
) 
select dealership_id 
, ntile(10) over(order by total_sales) 
from daily_sales

Hey Jacques,

Your code is really close again! The missing filter is indeed the issue here :slight_smile: Here’s the solution:

Solution
WITH total_dealership_sales AS (
    SELECT
        dealership_id,
        SUM(sales_amount) AS total_sales_amount
    FROM
        sales
    WHERE
        sales_transaction_date >= '2018-01-01'
        AND sales_transaction_date < '2019-01-01'
        AND channel = 'dealership'
    GROUP BY 1
)
SELECT
    *,
    NTILE(10) OVER (ORDER BY total_sales_amount)
FROM
    total_dealership_sales;

Hi Lorraine,

I’m sorry about the delay to getting back to you.

Thanks for your reply! I forgot the filter…

Have a good one! :slight_smile:

Jacques

1 Like

Hi Lorraine, I hope you’re doing good !

I’m sorry for disturbing you again, I’m having a small trouble with the 3rd question of the exercise. I gotta identify the top 5 best dealerships in terms of units sold, but I don’t know why my request is not right… Please, could you have a look ? :slight_smile:

Here is my code below:

select dealership_id
, count(1) as units_sold
from sales
where channel <> 'internet'
group by 1 
order by 2 desc
limit 5;

Thanks Lorraine :slight_smile:

Cheers

Jacques

Hey Jacques,

Your code is correct - our solution was missing the units_sold alias for the second column. I’ve gone in to fix this and your code should pass the check now.

So sorry about this!

Best,
Lorraine

Hi Lorraine,

Thanks for getting to me back again! I really appreciate it :slight_smile:

Ok, no worries, thanks a lot!

Have a good one :slight_smile:

Jacques

1 Like