SQL Solutions

** Occupations Problem ** - Level: Medium
With tab1 as (select 
       CASE WHEN Occupation = 'Doctor' then Name else NULL end as [Doctor],
       CASE WHEN Occupation = 'Professor' then Name else NULL end as [Professor],
       CASE WHEN Occupation = 'Singer' then Name else NULL end as [Singer],
       CASE WHEN Occupation = 'Actor' then Name else NULL end as [Actor]
from OCCUPATIONS),

NumberedRows as (
    Select 'Doctor' as OcType, Doctor as Occ, ROW_NUMBER() over (Order by Doctor asc) as rn       FROM tab1 
        where Doctor is not null
    UNION ALL
    Select 'Professor'as OcType, [Professor] as Occ,ROW_NUMBER() over (order by [Professor] asc )
      FROM tab1
        where Professor is not null
        UNION ALL
    Select 'Singer' as OcType, [Singer] as Occ,ROW_NUMBER() over (order by [Singer] asc)
      FROM tab1
        where [Singer] is not null
            UNION ALL
    Select 'Actor' as OcType, [Actor] as Occ,ROW_NUMBER() over (order by [Actor] asc)
      FROM tab1
        where [Actor] is not null

)
select
 MAX(CASE WHEN OcType = 'Doctor' THEN Occ END) AS Doc,
    MAX(CASE WHEN OcType = 'Professor' THEN Occ END) AS Pro,
    MAX(CASE WHEN OcType = 'Singer' THEN Occ END) AS Sing,
    MAX(CASE WHEN OcType = 'Actor' THEN Occ END) AS Act
FROM NumberedRows
GROUP BY rn
ORDER BY rn;



** Symmetric pairs - Level: Medium
WITH tab1 AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY X, Y) AS rn
  FROM Functions
)
SELECT A.X AS X1, A.Y AS Y1
FROM tab1 AS A
JOIN tab1 AS B ON A.X = B.Y AND A.Y = B.X
WHERE A.rn < B.rn AND A.X <= A.Y
ORDER BY A.X ASC;



*** Placements  - Level: Medium
With tab1 as (select a.ID, a.Name, c.Salary,b.ID as FriendID, d.Salary as FriendSalary
from Students a
left join Friends b
on a.ID = b.ID
left join Packages c
on a.ID = c.ID
left join Packages d
on b.Friend_ID = d.ID)
select Name
from tab1
where FriendSalary > Salary
order by FriendSalary asc



***15 days of learning SQL - Level:Advanced
-- Calculate daily submissions and align with total day count
WITH DailySubmissionAndDayCount AS (
    SELECT 
        unique_entries.submission_date, 
        unique_entries.hacker_id,
        COUNT(unique_entries.submission_date) OVER (
            PARTITION BY unique_entries.hacker_id 
            ORDER BY unique_entries.submission_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS total_count,
        day_order.nr AS day_number
    FROM (
        SELECT DISTINCT submission_date, hacker_id
        FROM Submissions
    ) AS unique_entries
    LEFT JOIN (
        SELECT 
            submission_date, 
            ROW_NUMBER() OVER (ORDER BY submission_date) AS nr
        FROM Submissions
        GROUP BY submission_date
    ) AS day_order ON unique_entries.submission_date = day_order.submission_date
),

-- Identify unique hackers with daily submissions
UniqueDailyHackers AS (
    SELECT 
        submission_date, 
        COUNT(DISTINCT hacker_id) AS total_unique_hacker
    FROM DailySubmissionAndDayCount
    WHERE total_count = day_number
    GROUP BY submission_date
),

-- Aggregate submissions by hacker and date
AggregatedSubmissions AS (
    SELECT 
        submission_date, 
        hacker_id, 
        COUNT(submission_id) AS submission_count
    FROM Submissions
    GROUP BY submission_date, hacker_id
),

-- Find the maximum submissions for each date
MaxSubmissionPerDate AS (
    SELECT 
        submission_date,  
        MAX(submission_count) AS max_submission_count
    FROM AggregatedSubmissions
    GROUP BY submission_date
),

-- Filter hackers with maximum submissions and resolve ties
TopHackersWithTieResolution AS (
    SELECT 
        aggregated.*,
        ROW_NUMBER() OVER (
            PARTITION BY aggregated.submission_date 
            ORDER BY aggregated.hacker_id ASC
        ) AS row_num
    FROM AggregatedSubmissions AS aggregated
    JOIN MaxSubmissionPerDate AS max_values ON aggregated.submission_date = max_values.submission_date
    WHERE aggregated.submission_count = max_values.max_submission_count
),

-- Select the top hacker for each date (resolve ties by lowest hacker_id)
TopHackerPerDate AS (
    SELECT submission_date, hacker_id
    FROM TopHackersWithTieResolution
    WHERE row_num = 1
),

-- Aggregate by date to find the top hacker with lowest hacker_id
FinalTopHackers AS (
    SELECT 
        submission_date, 
        MIN(hacker_id) AS top_hacker_id
    FROM TopHackerPerDate
    GROUP BY submission_date
)

-- Final Query: Combine unique daily hackers and top hackers, and join with hacker names
SELECT 
    UniqueDailyHackers.submission_date, 
    UniqueDailyHackers.total_unique_hacker, 
    FinalTopHackers.top_hacker_id, 
    Hackers.name
FROM UniqueDailyHackers
LEFT JOIN FinalTopHackers ON UniqueDailyHackers.submission_date = FinalTopHackers.submission_date
LEFT JOIN Hackers ON FinalTopHackers.top_hacker_id = Hackers.hacker_id
ORDER BY UniqueDailyHackers.submission_date;



*** SQL Project Planning - Level: Medium ***
WITH TaskWindow AS (
    SELECT 
        Start_Date,
        End_Date,
        LEAD(Start_Date, 1) OVER (ORDER BY Start_Date ASC) AS Next_Start_Date,
        LAG(End_Date, 1) OVER (ORDER BY End_Date ASC) AS Previous_End_Date,
        ROW_NUMBER() OVER (ORDER BY Start_Date ASC) AS RowNum
    FROM Projects
),
AggregatedProjects AS (
    SELECT 
        MIN(Start_Date) AS Project_Start_Date,
        MAX(End_Date) AS Project_End_Date
    FROM TaskWindow
    GROUP BY DATEDIFF(day, RowNum, Start_Date)
)
SELECT *
FROM AggregatedProjects
ORDER BY DATEDIFF(day, Project_Start_Date, Project_End_Date) ASC, Project_Start_Date ASC;