We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
WITH all_days AS (
SELECT DISTINCT submission_date
FROM submissions
WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
),
daily_loyalty AS (
SELECT d.submission_date AS ref_date, s.hacker_id, s.submission_date
FROM all_days d
JOIN submissions s ON s.submission_date <= d.submission_date
),
loyal_hackers AS (
SELECT ref_date, hacker_id, COUNT(DISTINCT submission_date) AS days_active
FROM daily_loyalty
GROUP BY ref_date, hacker_id
),
final_counts AS (
SELECT ref_date, COUNT(*) AS loyal_hacker_count
FROM loyal_hackers
WHERE days_active = DATEDIFF(DAY, '2016-03-01', ref_date) + 1
GROUP BY ref_date
),
count_date AS (
SELECT s.submission_date, count(s.hacker_id) AS totals, h.hacker_id, h.name
FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id
GROUP BY submission_date, h.hacker_id, h.name
),
max_date AS (
SELECT submission_date, max(totals) AS max_totals
FROM count_date
GROUP BY submission_date
),
min_hacker as (
SELECT c.submission_date, c.totals AS max_totals, hacker_id, name, fc.loyal_hacker_count AS totale
FROM count_date c
JOIN max_date m ON c.submission_date = m.submission_date AND c.totals = m.max_totals
JOIN final_counts fc ON c.submission_date = fc.ref_date
)
SELECT submission_date, totale, hacker_id, name
FROM min_hacker mh
WHERE hacker_id = (SELECT MIN(hacker_id)
FROM min_hacker m
WHERE m.submission_date = mh.submission_date)
ORDER BY submission_date;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →
MS SQL Server:
WITH all_days AS ( SELECT DISTINCT submission_date FROM submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' ), daily_loyalty AS ( SELECT d.submission_date AS ref_date, s.hacker_id, s.submission_date FROM all_days d JOIN submissions s ON s.submission_date <= d.submission_date ), loyal_hackers AS ( SELECT ref_date, hacker_id, COUNT(DISTINCT submission_date) AS days_active FROM daily_loyalty GROUP BY ref_date, hacker_id ), final_counts AS ( SELECT ref_date, COUNT(*) AS loyal_hacker_count FROM loyal_hackers WHERE days_active = DATEDIFF(DAY, '2016-03-01', ref_date) + 1 GROUP BY ref_date ), count_date AS ( SELECT s.submission_date, count(s.hacker_id) AS totals, h.hacker_id, h.name FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id GROUP BY submission_date, h.hacker_id, h.name ), max_date AS ( SELECT submission_date, max(totals) AS max_totals FROM count_date GROUP BY submission_date ), min_hacker as ( SELECT c.submission_date, c.totals AS max_totals, hacker_id, name, fc.loyal_hacker_count AS totale FROM count_date c JOIN max_date m ON c.submission_date = m.submission_date AND c.totals = m.max_totals JOIN final_counts fc ON c.submission_date = fc.ref_date ) SELECT submission_date, totale, hacker_id, name FROM min_hacker mh WHERE hacker_id = (SELECT MIN(hacker_id) FROM min_hacker m WHERE m.submission_date = mh.submission_date) ORDER BY submission_date;