How to compare average prices of weekday vs weekend

Question

How to compare average prices of weekday vs weekend

phonecase

Old Hand

Points: 330
More actions
November 13, 2022 at 4:26 am

#4112215

Hello,
I have a task where I need to find the task of a rental price where the rental price is different when it's a weekday vs the price when it's a weekend.
Here is sample of the data work set I'm working with
where listing_ID is the property
# listing_id, date, available, price
'3075044', '2017-08-22', 't', '65'
'3075044', '2017-08-21', 't', '65'
'3075044', '2017-08-20', 't', '65'
'3075044', '2017-08-19', 't', '75'
'3075044', '2017-08-18', 't', '75'
'3075044', '2017-08-17', 't', '65'
'3075044', '2017-08-16', 't', '65'
'3075044', '2017-08-15', 't', '65'
'3075044', '2017-08-14', 't', '65'
'3075044', '2017-08-13', 't', '65'
'3075044', '2017-08-12', 't', '75'
'3075044', '2017-08-11', 't', '75'
'3075044', '2017-08-10', 't', '65'
'3075044', '2017-07-20', 't', '65'
'3075044', '2017-07-19', 't', '65'
'3075044', '2017-07-18', 't', '65'
'3075044', '2017-07-17', 't', '65'
'3075044', '2017-07-16', 't', '65'
'3075044', '2017-07-15', 't', '75'
'3075044', '2017-07-14', 't', '75'
'3075044', '2017-07-13', 't', '65'
'3075044', '2017-07-12', 't', '65'
'3075044', '2017-07-11', 't', '65'
'3075044', '2017-07-10', 't', '65'
'3075044', '2017-07-09', 't', '65'
'3075044', '2017-07-08', 't', '75'
'3075044', '2017-07-07', 't', '75'
'3075044', '2017-07-06', 't', '65'
So far the query I have I can identify the average price per listing_ID, but I can't average just the weekend price vs the weekday price
select *, AVG(price) as AveragePrice
from RentalRates
Group by listing_id
order by listing_ID

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2227 More actions · Answer 1

A good first step could be to run this code

select datepart(weekday, cast(getdate()+nums.n as date)) day_of_week_number, 
       datename(weekday, cast(getdate()+nums.n as date)) day_of_week
from (values (0),(1),(2),(3),(4),(5),(6)) nums(n);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

Here is a suggestion that should get you passed this hurdle

😎

USE TEEST;
GO
SET NOCOUNT ON;
GO
---------------------------------------------------------------------
-- SAMPLE DATA AS POSTED
---------------------------------------------------------------------
;WITH SAMPLE_DATA(listing_id, [date], available, price) AS
(
    SELECT 
        X.listing_id
       ,CONVERT(DATE,X.[date],23) AS [date]
       ,X.available
       ,X.price
    FROM
        (VALUES
             (3075044, '2017-08-22', 't', 65.0)
            ,(3075044, '2017-08-21', 't', 65.0)
            ,(3075044, '2017-08-20', 't', 65.0)
            ,(3075044, '2017-08-19', 't', 75.0)
            ,(3075044, '2017-08-18', 't', 75.0)
            ,(3075044, '2017-08-17', 't', 65.0)
            ,(3075044, '2017-08-16', 't', 65.0)
            ,(3075044, '2017-08-15', 't', 65.0)
            ,(3075044, '2017-08-14', 't', 65.0)
            ,(3075044, '2017-08-13', 't', 65.0)
            ,(3075044, '2017-08-12', 't', 75.0)
            ,(3075044, '2017-08-11', 't', 75.0)
            ,(3075044, '2017-08-10', 't', 65.0)
            ,(3075044, '2017-07-20', 't', 65.0)
            ,(3075044, '2017-07-19', 't', 65.0)
            ,(3075044, '2017-07-18', 't', 65.0)
            ,(3075044, '2017-07-17', 't', 65.0)
            ,(3075044, '2017-07-16', 't', 65.0)
            ,(3075044, '2017-07-15', 't', 75.0)
            ,(3075044, '2017-07-14', 't', 75.0)
            ,(3075044, '2017-07-13', 't', 65.0)
            ,(3075044, '2017-07-12', 't', 65.0)
            ,(3075044, '2017-07-11', 't', 65.0)
            ,(3075044, '2017-07-10', 't', 65.0)
            ,(3075044, '2017-07-09', 't', 65.0)
            ,(3075044, '2017-07-08', 't', 75.0)
            ,(3075044, '2017-07-07', 't', 75.0)
            ,(3075044, '2017-07-06', 't', 65.0)
        ) X(listing_id, [date], available, price)
)
---------------------------------------------------------------------
-- PART #1
-- Tagging the weekdays as 1 and the weekend days as 2
---------------------------------------------------------------------
,BASE_DATA(listing_id, [date], DOW, available, price) AS
(
    SELECT
        SD.listing_id
       ,SD.[date]
       ,CASE 
            WHEN (DATEDIFF(DD,0,SD.[date]) % 7) > 4 THEN 2
            ELSE 1
        END AS DOW
       ,SD.available
       ,SD.price
    FROM SAMPLE_DATA SD
)
SELECT 
    BD.listing_id
   ,BD.[date]
   ,BD.DOW
   ,BD.available
   ,BD.price
   ,AVG(BD.price) OVER
        ( PARTITION BY
                        BD.listing_id
                       ,BD.DOW
        ) AS WD_AVG
FROM BASE_DATA BD;

Result set

listing_id  date       DOW  available price  WD_AVG
----------- ---------- ---- --------- ------ ----------
3075044     2017-08-22 1    t         65.0   67.000000
3075044     2017-08-21 1    t         65.0   67.000000
3075044     2017-07-07 1    t         75.0   67.000000
3075044     2017-07-10 1    t         65.0   67.000000
3075044     2017-08-18 1    t         75.0   67.000000
3075044     2017-08-17 1    t         65.0   67.000000
3075044     2017-08-16 1    t         65.0   67.000000
3075044     2017-08-15 1    t         65.0   67.000000
3075044     2017-08-14 1    t         65.0   67.000000
3075044     2017-07-11 1    t         65.0   67.000000
3075044     2017-07-12 1    t         65.0   67.000000
3075044     2017-08-11 1    t         75.0   67.000000
3075044     2017-08-10 1    t         65.0   67.000000
3075044     2017-07-20 1    t         65.0   67.000000
3075044     2017-07-19 1    t         65.0   67.000000
3075044     2017-07-18 1    t         65.0   67.000000
3075044     2017-07-17 1    t         65.0   67.000000
3075044     2017-07-13 1    t         65.0   67.000000
3075044     2017-07-14 1    t         75.0   67.000000
3075044     2017-07-06 1    t         65.0   67.000000
3075044     2017-07-16 2    t         65.0   70.000000
3075044     2017-08-12 2    t         75.0   70.000000
3075044     2017-08-13 2    t         65.0   70.000000
3075044     2017-08-19 2    t         75.0   70.000000
3075044     2017-07-09 2    t         65.0   70.000000
3075044     2017-07-08 2    t         75.0   70.000000
3075044     2017-08-20 2    t         65.0   70.000000
3075044     2017-07-15 2    t         75.0   70.000000

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Eirikur Eiriksson wrote:

Here is a suggestion that should get you passed this hurdle

Definitely a thumbs up for NOT using the awful DW datepart. Long live DateSerial# ZERO! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

phonecase wrote:

Hello,
I have a task where I need to find the task of a rental price where the rental price is different when it's a weekday vs the price when it's a weekend.
Here is sample of the data work set I'm working with where listing_ID is the property
So far the query I have I can identify the average price per listing_ID, but I can't average just the weekend price vs the weekday price
select *, AVG(price) as AveragePrice from RentalRates Group by listing_id order by listing_ID

Eirikur's reply does the trick by listingID and date. What are you expecting the output to be grouped by? Do you need it to be grouped by whole week or ??? so that the average weekday price and the average weekend price are on the same line of output for any given week or ???

Also, what are the datatypes for the 4 columns of data that you've posted? We need to be sure so that it all works correctly for your real data.

Last but not least, give the article at the first link in my signature line below a read to help us help you better in the future. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Sorry... something went wrong and so I took the post down. I'll be back.

This reply was modified 2 years, 10 months ago by Jeff Moden. Reason: Fixed output and code. Had the Weekdays and Weekends reversed
This reply was modified 2 years, 10 months ago by Jeff Moden. Reason: Error in post. Took the post down until I can fix it

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

I saw a "67" as an average and thought that was incorrect and that caused me to take the post down until I could double check. It's actually correct. Here's the post again...

Ok... just for fun... This is how a Data Analyst or Project Manager might like to see the output. It could also be turned into an iTVF for which dates and listings to include...

Here's the test data I used (thank you Eirikur for getting that started)...

--=====================================================================================================================
--      Create the test table.
--=====================================================================================================================
--===== Create and populate the test table on the fly.
     -- This is NOT a part of the solution.
     -- We're just building a test table here.
   DROP TABLE IF EXISTS #TestTable;
GO
 SELECT  v.listing_id
        ,[date] = CONVERT(DATE,v.[date],23)
        ,v.available
        ,v.price
   INTO #TestTable
   FROM (VALUES
         (3075044,'2017-08-22','t',65)
        ,(3075044,'2017-08-21','t',65)
        ,(3075044,'2017-08-20','t',65)
        ,(3075044,'2017-08-19','t',75)
        ,(3075044,'2017-08-18','t',75)
        ,(3075044,'2017-08-17','t',65)
        ,(3075044,'2017-08-16','t',65)
        ,(3075044,'2017-08-15','t',65)
        ,(3075044,'2017-08-14','t',65)
        ,(3075044,'2017-08-13','t',65)
        ,(3075044,'2017-08-12','t',75)
        ,(3075044,'2017-08-11','t',75)
        ,(3075044,'2017-08-10','t',65)
        ,(3075044,'2017-07-20','t',65)
        ,(3075044,'2017-07-19','t',65)
        ,(3075044,'2017-07-18','t',65)
        ,(3075044,'2017-07-17','t',65)
        ,(3075044,'2017-07-16','t',65)
        ,(3075044,'2017-07-15','t',75)
        ,(3075044,'2017-07-14','t',75)
        ,(3075044,'2017-07-13','t',65)
        ,(3075044,'2017-07-12','t',65)
        ,(3075044,'2017-07-11','t',65)
        ,(3075044,'2017-07-10','t',65)
        ,(3075044,'2017-07-09','t',65)
        ,(3075044,'2017-07-08','t',75)
        ,(3075044,'2017-07-07','t',75)
        ,(3075044,'2017-07-06','t',65)
        )v(listing_id,[date],available,price)
;
--===== Create another listing set to make sure
     -- it works for more than one listing_id
 INSERT INTO #TestTable
        (listing_id,[date],available,price)
 SELECT  listing_id = 4567890
        ,[date]
        ,available
        ,price = price * 1.5
   FROM #TestTable
;

Here's the code that generated the "report"...

--=====================================================================================================================
--      Create a more sophisticated report.
--=====================================================================================================================
--===== A bit more sophisticated report
 SELECT LineType             = CASE GROUPING_ID(src.listing_id,wd.SoW)
                               WHEN 0 THEN 'Weekly Listing_ID Averages'
                               WHEN 1 THEN 'Overall Average by Listing_ID'
                               WHEN 3 THEN 'Overall Average All Listings'
                               ELSE 'Unknown Grouping'
                               END
        ,[Listing ID]        = IIF(GROUPING_ID(src.listing_id,wd.SoW)<3,CONVERT(CHAR(10),src.listing_id),'----------')
        ,[Start Of Week]     = IIF(GROUPING_ID(src.listing_id,wd.SoW)=0,CONVERT(CHAR(10),wd.SoW,23),'----------')
        ,[End Of Week]       = IIF(GROUPING_ID(src.listing_id,wd.SoW)=0,CONVERT(CHAR(10),MAX(wd.Eow),23),'----------')
        ,[Avg Weekday Price] = AVG(IIF(wd.DW# < 6,src.price+0.0,NULL))
        ,[Avg Weekend Price] = AVG(IIF(wd.DW# > 5,src.price+0.0,NULL))
        ,[Avg Weekly Price]  = AVG(src.price+0.0)
        ,[Weekday Count]     = COUNT(IIF(wd.DW# < 6,src.price,NULL))
        ,[Weekend Count]     = COUNT(IIF(wd.DW# > 5,src.price,NULL))
        ,[Weekly Count]      = COUNT(src.price)
   FROM #TestTable src
  CROSS APPLY dbo.WeekDates([date],1) wd 
  GROUP BY GROUPING SETS (ROLLUP (src.listing_id, wd.SoW))
  ORDER BY GROUPING(src.listing_id),listing_id,GROUPING(wd.SoW),wd.SoW
;--=====================================================================================================================
--      Create a more sophisticated report.
--=====================================================================================================================
--===== A bit more sophisticated report
 SELECT LineType             = CASE GROUPING_ID(src.listing_id,wd.SoW)
                               WHEN 0 THEN 'Weekly Listing_ID Averages'
                               WHEN 1 THEN 'Overall Average by Listing_ID'
                               WHEN 3 THEN 'Overall Average All Listings'
                               ELSE 'Unknown Grouping'
                               END
        ,[Listing ID]        = IIF(GROUPING_ID(src.listing_id,wd.SoW)<3,CONVERT(CHAR(10),src.listing_id),'----------')
        ,[Start Of Week]     = IIF(GROUPING_ID(src.listing_id,wd.SoW)=0,CONVERT(CHAR(10),wd.SoW,23),'----------')
        ,[End Of Week]       = IIF(GROUPING_ID(src.listing_id,wd.SoW)=0,CONVERT(CHAR(10),MAX(wd.Eow),23),'----------')
        ,[Avg Weekday Price] = AVG(IIF(wd.DW# < 6,src.price+0.0,NULL))
        ,[Avg Weekend Price] = AVG(IIF(wd.DW# > 5,src.price+0.0,NULL))
        ,[Avg Weekly Price]  = AVG(src.price+0.0)
        ,[Weekday Count]     = COUNT(IIF(wd.DW# < 6,src.price,NULL))
        ,[Weekend Count]     = COUNT(IIF(wd.DW# > 5,src.price,NULL))
        ,[Weekly Count]      = COUNT(src.price)
   FROM #TestTable src
  CROSS APPLY dbo.WeekDates([date],1) wd 
  GROUP BY GROUPING SETS (ROLLUP (src.listing_id, wd.SoW))
  ORDER BY GROUPING(src.listing_id),listing_id,GROUPING(wd.SoW),wd.SoW
;

... and here's where you can get the dbo.WeekDates function that I used (1 = Monday in the second operand)...

https://www.sqlservercentral.com/articles/how-to-find-the-start-and-end-dates-for-the-current-week-and-more

If you just want to stick to the basics, like this...

... the dbo.WeekDates function makes it easy (pick your own total grouping and sort order)...

--=====================================================================================================================
--      Create the basic output requested 
--=====================================================================================================================
--===== Basic return by week
 SELECT  src.listing_id
        ,StartOfWeek     = wd.SoW
        ,EndOfWeek       = MAX(wd.Eow)
        ,AvgWeekdayPrice = AVG(IIF(wd.DW# < 6,src.price+0.0,NULL))
        ,AvgWeekendPrice = AVG(IIF(wd.DW# > 5,src.price+0.0,NULL))
   FROM #TestTable src
  CROSS APPLY dbo.WeekDates([date],1) wd
  GROUP BY src.listing_id,wd.SoW
  ORDER BY src.listing_id, StartOfWeek
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

phonecase Old Hand Points: 330 More actions · Answer 7

Thank you all, Steve Collins, Eirikur Eiriksson, and Jeff Moden for helping out

I've tagged the weekends and weekdays as follow

SELECT 
listing_id, 
CASE 
WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 1
ELSE 2
END AS DayType, 
AVG(price) AS AVGPRICE
FROM RentalRates
GROUP BY
listing_id, 
CASE 
WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 0 
ELSE 1 
END

Which gives me the result :

# listing_id DayType AVGPrice
3075044 1 66.8605
3075044 2 70.0000
5434353 1 145.0000
5434353 2 145.0000
7482195 1 49.0000
7482195 2 49.0000
4922204 1 205.0625
4922204 2 207.6667

I would like to only see the listing_id whenever DayType 1 and DayType 2's average price do not match when it's the same listing_ID

So my expected results would be that I see only listing_id 3075044 and listing_id 4922204 since the average price are different for the same listing. I don't need to see the other ones such as 5434353 and 7482195 since the average price of both the weekday and weekend are the same.

Jeff Moden wrote:
phonecase wrote:
Hello,
I have a task where I need to find the task of a rental price where the rental price is different when it's a weekday vs the price when it's a weekend.
Here is sample of the data work set I'm working with where listing_ID is the property
So far the query I have I can identify the average price per listing_ID, but I can't average just the weekend price vs the weekday price
select *, AVG(price) as AveragePrice from RentalRates Group by listing_id order by listing_ID
Eirikur's reply does the trick by listingID and date. What are you expecting the output to be grouped by? Do you need it to be grouped by whole week or ??? so that the average weekday price and the average weekend price are on the same line of output for any given week or ???
Also, what are the datatypes for the 4 columns of data that you've posted? We need to be sure so that it all works correctly for your real data.
Last but not least, give the article at the first link in my signature line below a read to help us help you better in the future. Thanks.
listing_id: int
date: text
available: text
price: int

thank you all once again for your help.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

phonecase wrote:

listing_id: int
date: text
available: text
price: int

I was right about price but, lordy... date and available are actually the TEXT datatype? Even if they're VARCHAR() is a pretty good design flaw. My recommendation is that the date column be changed to the DATE datatype. I don't know what else goes into "available" other than "t" but, if it's always going to be just one orto 5 characters, then I'd recommend CHAR() data type.

As for your latest request, if you take the output from the "basic" code I produced, it's easy enough to use that basic code as a CTE or Derived Table and just compare the two columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)