/* Contests Table*/
CREATE TABLE contests (
contest_id INT NOT NULL,
hacker_id INT NOT NULL,
name varchar(100) NOT NULL
);
INSERT INTO contests (contest_id, hacker_id, name) VALUES
('63963', '81041', 'Kristie'),
('63117', '79345', 'Vasilis'),
('28225', '34856', 'Eshan');
/* Colleges Table*/
CREATE TABLE colleges (
college_id INT NOT NULL,
contest_id INT NOT NULL);
INSERT INTO colleges (college_id, contest_id) VALUES
('12299', '63963'),
('34856', '63117'),
('79345', '28225');
/* Challenges Table*/
CREATE TABLE challenges (
challenge_id INT NOT NULL,
college_id INT NOT NULL);
INSERT INTO challenges (challenge_id, college_id) VALUES
('18765', '12299'),
('47127', '12299'),
('60292', '34856'),
('72974', '79345');
/* View_Stats Table*/
CREATE TABLE view_stats (
challenge_id INT NOT NULL,
c_views INT NOT NULL,
unique_views INT NOT NULL);
INSERT INTO view_stats (challenge_id, c_views, unique_views) VALUES
('18765', '12', '10'),
('47127', '22', '14'),
('60292', '5', '2'),
('72974', '15', '13'),
('18765', '8', '4'),
('47127', '45', '15'),
('60292', '21', '11'),
('72974', '35', '10');
SELECT con.contest_id, con.hacker_id, con.name, SUM(v.c_views), SUM(v.unique_views)
FROM contests con
LEFT JOIN colleges col
ON con.contest_id = col.contest_id
LEFT JOIN challenges ch
ON col.college_id = ch.college_id
LEFT JOIN view_stats v
ON ch.challenge_id = v.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING con.name = 'Kristie'
Telegram: DB developers channel