https://www.youtube.com/watch?v=02XLUeIVRSE&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=15

-- Create the family table
CREATE TABLE family (
    person VARCHAR(5),
    type VARCHAR(10),
    age INT
);

-- Delete existing records from the family table
DELETE FROM family;

-- Insert values into the family table
INSERT INTO family (person, type, age) VALUES
    ('A1', 'Adult', 54),
    ('A2', 'Adult', 53),
    ('A3', 'Adult', 52),
    ('A4', 'Adult', 58),
    ('A5', 'Adult', 54),
    ('C1', 'Child', 20),
    ('C2', 'Child', 19),
    ('C3', 'Child', 22),
    ('C4', 'Child', 15);

-- They are going fr a ride and need to be paired up like an adult with a child. An adult can remain solo at the end.

WITH
ROW_NUMBER()

WITH adult_cte AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY PERSON) AS rn FROM family WHERE type = 'Adult'), Child_cte AS 
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY PERSON) AS rn FROM family WHERE type = 'Child'
)

SELECT a.person,c.person
FROM adult_cte a
LEFT JOIN child_cte c
ON a.rn = c.rn

-- Suppose the oldest one has to go with youngest child, 2nd oldest with 2nd youngest...and so on... (change in order by of row_number)
WITH adult_cte AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY age desc) AS rn FROM family WHERE type = 'Adult'), Child_cte AS 
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY age asc) AS rn FROM family WHERE type = 'Child'
)

SELECT a.person,c.person,a.age,c.age
FROM adult_cte a
LEFT JOIN child_cte c
ON a.rn = c.rn