SQL Query help

Transact-SQL
1

Hi,

I am looking for some help with two queries using the following tables/data. It would be great to see the most efficient way to achieve these.

Queries:

1: Show all homes with rent above the average rent value for their Company

2: Show the number of properties registered 0-3 months, 4-6 months, 6-12 months, 1- 2 years, and 2+ years ago

Thank you for looking.



CREATE SCHEMA homes;

GO

CREATE TABLE homes.Companies (CompanyId INT, CompanyName NVARCHAR(50));

INSERT INTO homes.Companies VALUES
 (1,'Company A')
,(2,'Company B')
,(3,'Company C')
,(4,'Company D')

CREATE TABLE homes.Branches(BranchId INT, CompanyId INT, BranchName NVARCHAR(20));
INSERT INTO homes.Branches VALUES
 (1,1,'Liverpool')
,(2,1,'Blackpool')
,(3,1,'Stockport')
,(4,2,'Durham')
,(5,2,'Oxford')
,(6,2,'Cambridge')
,(7,2,'Manchester')
,(8,3,'Newcastle')
,(9,3,'Hull')
,(10,3,'York')
,(11,4,'Birmingham')
,(12,4,'Stoke on Trent')


-- DROP TABLE homes.Properties;
CREATE TABLE homes.Properties (PropertyId INT, BranchId INT, Address NVARCHAR(200), Rent INT, RegisteredOn DATETIME);
INSERT INTO homes.Properties VALUES
 (1,1,'12a, Station Street, Liverpool',500,CAST('2019-12-31' AS DATETIME))
,(2,1,'12c, Station Street, Liverpool',475,CAST('2021-06-15' AS DATETIME))
,(3,3,'39, Main Road, Stockport',410,CAST('2022-09-30' AS DATETIME))
,(4,3,'12, Dovetrees, Manchester',700,CAST('2022-08-19' AS DATETIME))
,(5,5,'16, St Giles, Oxford',825,CAST('2023-02-28' AS DATETIME))
,(6,5,'32, George Street, Oxford',1100,CAST('2022-04-01' AS DATETIME))
,(7,5,'16, Main Road, Eynsham',680,CAST('2021-03-24' AS DATETIME))
,(8,6,'3b, University Road, Cambridge',960,CAST('2022-01-14' AS DATETIME))
,(9,7,'98, Heaton Road, Manchester',795,CAST('2022-09-07' AS DATETIME))
,(10,8,'64, St James Park, Newcastle',550,CAST('2022-11-26' AS DATETIME))
,(11,8,'47, St James Park, Newcastle',630,CAST('2023-01-16' AS DATETIME))
,(12,10,'6b, Station Lane, York',520,CAST('2022-07-19' AS DATETIME))
,(13,10,'24, Newcastle Road, York',510,CAST('2022-03-15' AS DATETIME))
,(14,11,'64, Bull Ring, Birmingham',1025,CAST('2022-12-03' AS DATETIME))
,(15,12,'Willowmead, Pottery Lane, Stoke on Trent',765,CAST('2022-05-12' AS DATETIME))
2

create view homes.v_RentAge AS
SELECT
CompanyId,
CompanyName,
BranchId,
BranchName,
PropertyId,
Address,
Rent,
RegisteredOn,
CASE
WHEN Age < 4 THEN 3
WHEN Age < 7 THEN 6
WHEN Age < 13 THEN 12
WHEN Age < 25 THEN 24
ELSE 25
END Age
FROM
(
SELECT
C.CompanyId,
C.CompanyName,
B.BranchId,
B.BranchName,
P.PropertyId,
P.Address,
P.Rent,
P.RegisteredOn,
DATEDIFF(MONTH, P.RegisteredOn, GETDATE()) AS Age
FROM
homes.Companies AS C
INNER JOIN homes.Branches AS B ON B.CompanyId = C.CompanyId
INNER JOIN homes.Properties AS P ON P.BranchId = B.BranchId
) AS subquery

select AVG (rent) AvgRent, Age from homes.v_RentAge
group by Age

AvgRent Age

826 6
595 12
762 24
590 25

This should do it for thousands, or even tens-of-thousands, but if you have millions you can improve the performance by creating a table of dates on which to join, create clustered indexes on RegisteredOn, and modify the query to retrieve only the selected date range you are looking for to allow clustered index scan rather than an entire table scan. Since you have no times, it may be efficient to create a date table to allow index seek rather than scan, depending on the index statistics. On the other hand, it may not provide any benefit. You could also create a persistent computed column called Age in your Properties table. For the most efficient, store the julian date DATEDIFF(DAY, '1900-01-01', RegisteredOn) + 1 AS JulianDate in addition to the gregorian date and index that along with the Rent column. That would support tens of millions of rows. NB: I'm an old man. Not up to date on recent SQL techniques.

3 

--#2
SELECT ca2.Age, COUNT(*) AS PropertyCount
FROM homes.Properties P
CROSS APPLY (
    SELECT DATEDIFF(MONTH, P.RegisteredOn, GETDATE()) AS AgeInMonths
) AS ca1
CROSS APPLY (
    SELECT CASE 
        WHEN AgeInMonths <=  3 THEN '0-3 months'
        WHEN AgeInMonths <=  6 THEN '4-6 months'
        WHEN AgeInMonths <= 12 THEN '6-12 months'
        WHEN AgeInMonths <= 24 THEN '1-2 years'
        ELSE '2+ years ago' END AS Age
) AS ca2
GROUP BY ca2.Age
1 Like
4

Hi Scott. Do you remember me? You worked at a paper company 20 years ago. We spent a lot of time on Experts Exchange back in the day, and I was also very active here. I retired 7 years ago. Not much SQL any more.

5

Yes, I remember. I did work at IP then :slight_smile:.

6

I remembered that because my father-in-law was a programmer for IP in Natchez MS.

7

SQL Query is a powerful tool for retrieving and manipulating data in relational databases. It allows users to define specific criteria and conditions to extract relevant information from large datasets efficiently. By using SQL Query, developers and analysts can perform tasks such as retrieving, inserting, updating, and deleting data with ease. With its standardized syntax and broad support across database systems, SQL Query empowers users to extract valuable insights, generate reports, and make data-driven decisions, making it an essential skill in the realm of database management and analysis.

8

Hi everyone.

As I understood correctly, we are looking for rents that are above the average the rent for their Companies. Here is what I got:

--AVG Rent as per the company

SELECT
C.CompanyId AS [CompanyId],
AVG(P.Rent) AS [Average_Rent]
FROM #Branches B
INNER JOIN #Companies C
ON B.CompanyId = C.CompanyId
INNER JOIN #Properties P
ON P.BranchId = B.BranchId
GROUP BY C.CompanyId

--Rents above average per company

;WITH CTE1

AS
(

SELECT
C.CompanyId AS [CompanyId],
AVG(P.Rent) AS [Average_Rent]
FROM #Branches B
INNER JOIN #Companies C
ON B.CompanyId = C.CompanyId
INNER JOIN #Properties P
ON P.BranchId = B.BranchId
GROUP BY C.CompanyId
),

CTE2
AS

(

SELECT
C.CompanyId AS [CompanyId],
P.Rent AS [Rent]
FROM #Branches B
INNER JOIN #Companies C
ON B.CompanyId = C.CompanyId
INNER JOIN #Properties P
ON P.BranchId = B.BranchId
)

SELECT * FROM CTE2
WHERE CTE2.Rent > (SELECT [Average_Rent] FROM CTE1
WHERE CTE1.CompanyId = CTE2.CompanyId)
ORDER BY CompanyId