-- Drop existing tables if they exist
DROP TABLE IF EXISTS Contests;
DROP TABLE IF EXISTS Colleges;
DROP TABLE IF EXISTS Challenges;
DROP TABLE IF EXISTS View_Stats;
DROP TABLE IF EXISTS Submission_Stats;

-- Create Contests table
CREATE TABLE Contests (
    contest_id INT PRIMARY KEY,
    hacker_id INT,
    name VARCHAR(100)
);

-- Create Colleges table
CREATE TABLE Colleges (
    college_id INT PRIMARY KEY,
    contest_id INT,
    FOREIGN KEY (contest_id) REFERENCES Contests(contest_id)
);

-- Create Challenges table
CREATE TABLE Challenges (
    challenge_id INT PRIMARY KEY,
    college_id INT,
    FOREIGN KEY (college_id) REFERENCES Colleges(college_id)
);

-- Create View_Stats table
CREATE TABLE View_Stats (
    challenge_id INT,
    total_views INT,
    total_unique_views INT,
    FOREIGN KEY (challenge_id) REFERENCES Challenges(challenge_id)
);

-- Create Submission_Stats table
CREATE TABLE Submission_Stats (
    challenge_id INT,
    total_submissions INT,
    total_accepted_submissions INT,
    FOREIGN KEY (challenge_id) REFERENCES Challenges(challenge_id)
);

-- Insert sample data into Contests
INSERT INTO Contests (contest_id, hacker_id, name) VALUES
(66406, 17973, 'Rose'),
(66556, 79153, 'Angela'),
(94828, 80275, 'Frank');

-- Insert sample data into Colleges
INSERT INTO Colleges (college_id, contest_id) VALUES
(11219, 66406),
(32473, 66556),
(56685, 94828);

-- Insert sample data into Challenges
INSERT INTO Challenges (challenge_id, college_id) VALUES
(18765, 11219),
(47127, 11219),
(60292, 32473),
(72974, 56685);

-- Insert sample data into View_Stats
INSERT INTO View_Stats (challenge_id, total_views, total_unique_views) VALUES
(47127, 26, 19),
(47127, 15, 14),
(18765, 43, 10),
(18765, 72, 13),
(60292, 11, 10),
(72974, 41, 15);

-- Insert sample data into Submission_Stats
INSERT INTO Submission_Stats (challenge_id, total_submissions, total_accepted_submissions) VALUES
(47127, 27, 10),
(47127, 56, 18),
(72974, 68, 24),
(72974, 82, 14);

with main_cte as(
    select t1.challenge_id,t2.college_id,t2.contest_id
    from Challenges t1 left join Colleges t2
    on t1.college_id = t2.college_id
),
view_stats_gb as(
    select t2.contest_id, sum(t1.total_views) as total_views,
    sum(t1.total_unique_views) as total_unique_views
    from View_Stats t1 join
    main_cte t2 on t1.challenge_id = t2.challenge_id
    group by t2.contest_id
),
Submission_Stats_gb as (
    select t2.contest_id, sum(t1.total_submissions) as total_submissions,
    sum(t1.total_accepted_submissions) as total_accepted_submissions
    from Submission_Stats  t1 join
    main_cte t2 on t1.challenge_id = t2.challenge_id
    group by t2.contest_id
)
select t1.*,
t3.total_submissions,
t3.total_accepted_submissions,
t2.total_views,
t2.total_unique_views
from Contests t1 
join view_stats_gb t2 on  t1.contest_id = t2.contest_id
join Submission_Stats_gb t3 on t1.contest_id = t3.contest_id
where 
(t3.total_submissions +
    t3.total_accepted_submissions +
    t2.total_views +
    t2.total_unique_views) <> 0
order by t1.contest_id;