SQLTeam.com | Weblogs | Forums

How to compare average of weekday vs weekend price

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 the data work set I'm working with
where listing_ID is the property

image

So far the query I have I can identify the average price per listing_ID

To find if the data is a weekday or not you can use the function DATEPART and use the option WEEKDAY.

It will look like something like this:

SELECT 
prop.listing_id, 
CASE 
WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 0 
ELSE 1 
END AS WEEKDAY, 
AVG(price) AS AVG_price
FROM property prop
GROUP BY
prop.listing_id, 
CASE 
WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 0 
ELSE 1 
END
1 Like

Hey RogierPronk,

Thanks for the tip

Based on your query, I get the results like this
image

Any additional tip for me to display only the listing_id when the average prices don't match up?

Based on the screenshot above, I would like only listing_id 3075044 and 4922204 to show up since they are the only listings where the price of the weekday and weekend are different.

where as listing_ID 5434353 and 7482195 weekday and weekend prices are the same so I would like to omit those ones

I'm thinking of writing an additional CASE statement, but unsure how exactly to write it.

Thanks again for your help! Really appreciate it!

hi hope this helps

; with cte as 
(
SELECT 
	prop.listing_id,  
	CASE WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 0 ELSE 1 END AS WEEKDAY, 
	AVG(price) AS AVG_price
FROM 
   property prop
GROUP BY
	prop.listing_id, 
	CASE WHEN DATEPART(WEEKDAY,date) IN (1,7) THEN 0 ELSE 1 END
)
select 
    * 
from 
    (select * from cte where WEEKDAY = 0 ) a 
	   join 
    (select * from cte where WEEKDAY = 1 ) b 
	     on a.id = b.id 
where 
    a.AVG_price <> b.AVG_price