How to compare average of weekday vs weekend price

1

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

2

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
3

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!

4

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