Advanced SQL query help

  • Hi,

    I am looking for some help with two queries using the following table/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 homes 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))

  • Homework, school assignment, ... try chat gpt

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Here is a formatted version. Note that all of your

    CAST ('YYYY-MM-DD' AS DATETIME)

    statements can be simplified to 'YYYYMMDD'. As suggested above, this looks like homework to me.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You may get better replies if you show what you have tried already, and what part is causing problems.

    Expecting the entire solution from scratch seems a bit much.

  • Do you need to convert the dates ?  Isn't this OK ?

    INSERT INTO homes.Properties
    VALUES
    (1, 1, '12a, Station Street, Liverpool', 500, '2019-12-31' )
    ,(2, 1, '12c, Station Street, Liverpool', 475, '2021-06-15' )
    ,(3, 3, '39, Main Road, Stockport', 410, '2022-09-30' )
    ,(4, 3, '12, Dovetrees, Manchester', 700, '2022-08-19' )

    • This reply was modified 4 months ago by  homebrew01.
  • homebrew01 wrote:

    Do you need to convert the dates ?  Isn't this OK ?

    INSERT INTO homes.Properties
    VALUES
    (1, 1, '12a, Station Street, Liverpool', 500, '2019-12-31' )
    ,(2, 1, '12c, Station Street, Liverpool', 475, '2021-06-15' )
    ,(3, 3, '39, Main Road, Stockport', 410, '2022-09-30' )
    ,(4, 3, '12, Dovetrees, Manchester', 700, '2022-08-19' )

    Looking at the provided data - I assume they are using a British English system, and if so - then no you cannot use '2019-12-31' for a datetime data type.  If the system is not using British English then it will work but it isn't safe.  In fact, the original code isn't safe using CAST either.

    Changing it to '20191231' would be safe - as then the format will be correctly converted as YYYYMMDD and is not language dependent.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • For the first query ChatGPT-4 generated

    WITH CompanyAverageRent AS (
    SELECT c.CompanyId, AVG(p.Rent) AS AverageRent
    FROM #Companies c
    JOIN #Branches b ON c.CompanyId = b.CompanyId
    JOIN #Properties p ON b.BranchId = p.BranchId
    GROUP BY c.CompanyId
    )
    SELECT p.PropertyId, p.Address, p.Rent, c.CompanyName
    FROM #Properties p
    JOIN #Branches b ON p.BranchId = b.BranchId
    JOIN #Companies c ON b.CompanyId = c.CompanyId
    JOIN CompanyAverageRent car ON c.CompanyId = car.CompanyId
    WHERE p.Rent > car.AverageRent
    ORDER BY c.CompanyName, p.Rent DESC;

    Imo it's an ok query.  The CTE is not really necessary imo

    select c.CompanyName, calc.avg_rent, above.*
    from #Companies c
    cross apply (select avg(p.rent)
    from #branches b
    join #Properties p on b.BranchId = p.BranchId
    where b.CompanyId=c.CompanyId) calc(avg_rent)
    cross apply (select p.PropertyId, p.Address, p.Rent
    from #branches b
    join #Properties p on b.BranchId = p.BranchId
    where b.CompanyId=c.CompanyId
    and p.Rent>calc.avg_rent) above(PropertyId, Address, Rent)
    order by c.CompanyName, above.Rent desc;

    For the second query ChatGPT-4 generated

    DECLARE @CurrentDate DATETIME = GETDATE();

    WITH RegistrationPeriods AS (
    SELECT PropertyId, DATEDIFF(MONTH, RegisteredOn, @CurrentDate) ddif,
    CASE
    WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 0 AND 3 THEN '0-3 months'
    WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 4 AND 6 THEN '4-6 months'
    WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 7 AND 12 THEN '6-12 months'
    WHEN DATEDIFF(YEAR, RegisteredOn, @CurrentDate) BETWEEN 1 AND 2 THEN '1-2 years'
    ELSE '2+ years'
    END AS RegistrationPeriod
    FROM #Properties
    )
    SELECT RegistrationPeriod, COUNT(PropertyId) AS NumberOfHomes
    FROM RegistrationPeriods
    GROUP BY RegistrationPeriod
    ORDER BY MIN(PropertyId);

    The variable declaration seems unnecessary and the CTE is unnecessary.  Mixing different date parts within DATEDIFF in the CASE expression and then using GROUP BY on the result seems potentially problematic imo.  The ORDER BY MIN(PropertyId) is useless and possibly wrong.

    Maybe something like this

    select reg.reg_period, count(p.PropertyId) AS NumberOfHomes
    from #Properties p
    cross apply (values (datediff(month, p.RegisteredOn, getdate()))) v(mo_diff)
    cross apply (values (case when v.mo_diff between 0 and 3 then '01 0-3 months'
    when v.mo_diff between 4 and 6 then '02 4-6 months'
    when v.mo_diff between 7 and 12 then '03 7-12 months'
    when v.mo_diff between 13 and 24 then '04 13-24 months'
    else '05 25+ months' end)) reg(reg_period)
    group by reg.reg_period
    order by reg.reg_period;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thanks Steve, That is a great reply. I haven't tried out ChatGPT. Really appreciate your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply