Cities With The Most Expensive Homes

#10315

Write a query that identifies cities with higher than average home prices when compared to the national average. Output the city names.

table name: zillow_transactions

Solution:

with cte as (
select city,
avg(mkt_price) over (partition by city) as avgCityPrice,
avg(mkt_price) over (partition by state) as avgStatePrice
from zillow_transactions
)
select distinct city from cte 
where avgCityPrice>avgStatePrice

Output:


SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[zillow_transactions]    Script Date: 12-01-2024 15:41:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[zillow_transactions](
	[id] [int] NULL,
	[state] [varchar](50) NULL,
	[city] [varchar](50) NULL,
	[street_address] [varchar](50) NULL,
	[mkt_price] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (1, N'NY', N'New York City', N'66 Trout Drive', 449761)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (2, N'NY', N'New York City', N'Atwater', 277527)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (3, N'NY', N'New York City', N'58 Gates Street', 268394)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (4, N'NY', N'New York City', N'Norcross', 279929)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (5, N'NY', N'New York City', N'337 Shore Ave.', 151592)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (6, N'NY', N'New York City', N'Plainfield', 624531)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (7, N'NY', N'New York City', N'84 Central Street', 267345)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (8, N'NY', N'New York City', N'Passaic', 88504)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (9, N'NY', N'New York City', N'951 Fulton Road', 270476)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (10, N'NY', N'New York City', N'Oxon Hill', 118112)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (11, N'CA', N'Los Angeles', N'692 Redwood Court', 150707)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (12, N'CA', N'Los Angeles', N'Lewiston', 463180)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (13, N'CA', N'Los Angeles', N'8368 West Acacia Ave.', 538865)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (14, N'CA', N'Los Angeles', N'Pearl', 390896)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (15, N'CA', N'Los Angeles', N'8206 Old Riverview Rd.', 117754)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (16, N'CA', N'Los Angeles', N'Seattle', 424588)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (17, N'CA', N'Los Angeles', N'7227 Joy Ridge Rd.', 156850)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (18, N'CA', N'Los Angeles', N'Battle Ground', 643454)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (19, N'CA', N'Los Angeles', N'233 Bedford Ave.', 713841)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (20, N'CA', N'Los Angeles', N'Saint Albans', 295852)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (21, N'IL', N'Chicago', N'8830 Baker St.', 12944)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (22, N'IL', N'Chicago', N'Watertown', 410766)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (23, N'IL', N'Chicago', N'632 Princeton St.', 160696)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (24, N'IL', N'Chicago', N'Waxhaw', 464144)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (25, N'IL', N'Chicago', N'7773 Tailwater Drive', 129393)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (26, N'IL', N'Chicago', N'Bonita Springs', 174886)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (27, N'IL', N'Chicago', N'31 Summerhouse Rd.', 296008)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (28, N'IL', N'Chicago', N'Middleburg', 279000)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (29, N'IL', N'Chicago', N'273 Windfall Avenue', 424846)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (30, N'IL', N'Chicago', N'Graham', 592268)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (31, N'TX', N'Houston', N'91 Canterbury Dr.', 632014)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (32, N'TX', N'Houston', N'Dallas', 68868)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (33, N'TX', N'Houston', N'503 Elmwood St.', 454184)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (34, N'TX', N'Houston', N'Kennewick', 186280)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (35, N'TX', N'Houston', N'739 Chapel Street', 334474)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (36, N'TX', N'Houston', N'San Angelo', 204460)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (37, N'TX', N'Houston', N'572 Parker Dr.', 678443)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (38, N'TX', N'Houston', N'Bellmore', 401090)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (39, N'TX', N'Houston', N'8653 South Oxford Street', 482214)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (40, N'TX', N'Houston', N'Butler', 330868)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (41, N'AZ', N'Phoenix', N'8667 S. Joy Ridge Court', 316291)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (42, N'AZ', N'Phoenix', N'Torrance', 210392)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (43, N'AZ', N'Phoenix', N'35 Harvard St.', 167502)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (44, N'AZ', N'Phoenix', N'Nutley', 327554)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (45, N'AZ', N'Phoenix', N'7313 Vermont St.', 285135)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (46, N'AZ', N'Phoenix', N'Lemont', 577667)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (47, N'AZ', N'Phoenix', N'8905 Buttonwood Dr.', 212301)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (48, N'AZ', N'Phoenix', N'Lafayette', 317504)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (49, N'AZ', N'Phoenix', N'170 Brandywine Drive', 287864)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (50, N'AZ', N'Phoenix', N'Lake Mary', 822532)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (51, N'PA', N'Philadelphia', N'77 West Mayfield St.', 541423)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (52, N'PA', N'Philadelphia', N'Port Chester', 334229)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (53, N'PA', N'Philadelphia', N'92 Bedford Lane', 445591)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (54, N'PA', N'Philadelphia', N'Palm Bay', 461683)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (55, N'PA', N'Philadelphia', N'86 Edgemont Drive', 386885)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (56, N'PA', N'Philadelphia', N'Hollywood', 670135)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (57, N'PA', N'Philadelphia', N'946 George St.', 416003)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (58, N'PA', N'Philadelphia', N'Braintree', 477100)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (59, N'PA', N'Philadelphia', N'35 Sussex Court', 293291)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (60, N'PA', N'Philadelphia', N'Henderson', 384877)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (61, N'CA', N'San Diego', N'222 N. Tunnel Ave.', 140981)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (62, N'CA', N'San Diego', N'Orange', 465564)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (63, N'CA', N'San Diego', N'757 Lakewood Street', 235446)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (64, N'CA', N'San Diego', N'Ronkonkoma', 143486)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (65, N'CA', N'San Diego', N'562 Marlborough Drive', 609823)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (66, N'CA', N'San Diego', N'Fitchburg', 202015)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (67, N'CA', N'San Diego', N'7487 North Market Drive', 455428)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (68, N'CA', N'San Diego', N'El Paso', 252083)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (69, N'CA', N'San Diego', N'672 High Ridge Drive', 506868)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (70, N'CA', N'San Diego', N'Great Falls', 90000)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (71, N'CA', N'San Francisco', N'9936 SW. Livingston Street', 217451)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (72, N'CA', N'San Francisco', N'Clearwater', 1794282)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (73, N'CA', N'San Francisco', N'76 South Ave.', 1692646)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (74, N'CA', N'San Francisco', N'Bethel Park', 596998)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (75, N'CA', N'San Francisco', N'51 Homestead Ave.', 1495620)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (76, N'CA', N'San Francisco', N'Valparaiso', 1752703)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (77, N'CA', N'San Francisco', N'7851 Lookout Court', 1383822)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (78, N'CA', N'San Francisco', N'Seymour', 243464)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (79, N'CA', N'San Francisco', N'8290 Bradford Street', 1568759)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (80, N'CA', N'San Francisco', N'Sugar Land', 28285)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (81, N'CA', N'Santa Clara', N'9673 South Homewood St.', 1119747)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (82, N'CA', N'Santa Clara', N'Mount Juliet', 204832)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (83, N'CA', N'Santa Clara', N'73 East South Ave.', 917268)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (84, N'CA', N'Santa Clara', N'Hudson', 713266)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (85, N'CA', N'Santa Clara', N'669 South Columbia St.', 974397)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (86, N'CA', N'Santa Clara', N'Hummelstown', 944574)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (87, N'CA', N'Santa Clara', N'7699 S. Orange St.', 1100325)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (88, N'CA', N'Santa Clara', N'Asbury Park', 966673)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (89, N'CA', N'Santa Clara', N'19 Beacon Circle', 1207658)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (90, N'CA', N'Santa Clara', N'Phillipsburg', 1682459)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (91, N'CA', N'Mountain View', N'730 El Dorado Street', 893838)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (92, N'CA', N'Mountain View', N'Bear', 446689)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (93, N'CA', N'Mountain View', N'8233 Swanson Circle', 1271223)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (94, N'CA', N'Mountain View', N'Lumberton', 949895)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (95, N'CA', N'Mountain View', N'8803 University Drive', 1376870)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (96, N'CA', N'Mountain View', N'Sterling Heights', 1235082)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (97, N'CA', N'Mountain View', N'9447 Rockwell St.', 1569111)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (98, N'CA', N'Mountain View', N'Biloxi', 853914)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (99, N'CA', N'Mountain View', N'219 East Rock Maple Ave.', 746615)
GO
INSERT [dbo].[zillow_transactions] ([id], [state], [city], [street_address], [mkt_price]) VALUES (100, N'CA', N'Mountain View', N'Bridgewater', 616129)
GO


Comments (0)
Questions List