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))

SELECT p.PropertyId, b.BranchName, p.Address, p.Rent
FROM homes.Properties p
JOIN homes.Branches b ON p.BranchId = b.BranchId
JOIN (
    SELECT c.CompanyId, AVG(p.Rent) AS AvgRent
    FROM homes.Properties p
    JOIN homes.Branches b ON p.BranchId = b.BranchId
    JOIN homes.Companies c ON b.CompanyId = c.CompanyId
    GROUP BY c.CompanyId
) AS subquery ON b.CompanyId = subquery.CompanyId
WHERE p.Rent > subquery.AvgRent;

SELECT
    CASE
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 3 THEN '0-3 months'
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 6 THEN '4-6 months'
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 12 THEN '6-12 months'
        WHEN DATEDIFF(YEAR, p.RegisteredOn, GETDATE()) <= 2 THEN '1-2 years'
        ELSE '2+ years'
    END AS TimeRange,
    COUNT(*) AS HomeCount
FROM homes.Properties p
GROUP BY
    CASE
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 3 THEN '0-3 months'
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 6 THEN '4-6 months'
        WHEN DATEDIFF(MONTH, p.RegisteredOn, GETDATE()) <= 12 THEN '6-12 months'
        WHEN DATEDIFF(YEAR, p.RegisteredOn, GETDATE()) <= 2 THEN '1-2 years'
        ELSE '2+ years'
    END;