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 DATA_TANGGAL AS (
SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS
WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15'
), DATA_HACKERS_MAX AS (
SELECT a.HACKER_ID, a.NAME, MIN(a.SUBMISSION_DATE) AS MIN_SUBMISSION_DATE
FROM (SELECT DISTINCT SUBMISSION_DATE, HACKER_ID, NAME FROM HACKERS
CROSS JOIN DATA_TANGGAL) A
LEFT JOIN (select distinct SUBMISSION_DATE, HACKER_ID from SUBMISSIONS
WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15'
) B
ON A.HACKER_ID = B.HACKER_ID AND A.SUBMISSION_DATE = B.SUBMISSION_DATE
WHERE B.HACKER_ID IS NULL
GROUP BY a.HACKER_ID, a.NAME
)
, COUNT_DATA AS (
SELECT SUBMISSION_DATE,COUNT(*) AS TOTAL FROM (
SELECT DISTINCT A.SUBMISSION_DATE ,A.HACKER_ID FROM SUBMISSIONS A
INNER JOIN DATA_TANGGAL B
ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
LEFT JOIN DATA_HACKERS_MAX C
ON A.HACKER_ID = C.HACKER_ID
WHERE C.HACKER_ID IS NULL OR A.SUBMISSION_DATE <= C.MIN_SUBMISSION_DATE
) A
GROUP BY SUBMISSION_DATE
), MAX_DATA AS (
SELECT SUBMISSION_DATE,HACKER_ID,NAME,COUNT(SCORE) AS TOTAL FROM (
SELECT DISTINCT A.SUBMISSION_DATE ,SUBMISSION_ID,NAME,A.HACKER_ID, SCORE FROM SUBMISSIONS A
INNER JOIN DATA_TANGGAL B
ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
INNER JOIN HACKERS C
ON A.HACKER_ID = C.HACKER_ID
) A
GROUP BY SUBMISSION_DATE, HACKER_ID, NAME
), MAX_DATA_FINAL AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SUBMISSION_DATE ORDER BY TOTAL DESC, HACKER_ID ASC) AS RN
FROM MAX_DATA
)
SELECT A.SUBMISSION_DATE, B. TOTAL, C.HACKER_ID, C.NAME
FROM DATA_TANGGAL A
LEFT JOIN COUNT_DATA B
ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
LEFT JOIN MAX_DATA_FINAL C
ON A.SUBMISSION_DATE = C.SUBMISSION_DATE
WHERE RN = 1
ORDER BY A.SUBMISSION_DATE ASC
go
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 →
SET NOCOUNT ON;
WITH DATA_TANGGAL AS ( SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15'
), DATA_HACKERS_MAX AS ( SELECT a.HACKER_ID, a.NAME, MIN(a.SUBMISSION_DATE) AS MIN_SUBMISSION_DATE FROM (SELECT DISTINCT SUBMISSION_DATE, HACKER_ID, NAME FROM HACKERS CROSS JOIN DATA_TANGGAL) A LEFT JOIN (select distinct SUBMISSION_DATE, HACKER_ID from SUBMISSIONS
WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15' ) B ON A.HACKER_ID = B.HACKER_ID AND A.SUBMISSION_DATE = B.SUBMISSION_DATE WHERE B.HACKER_ID IS NULL GROUP BY a.HACKER_ID, a.NAME ) , COUNT_DATA AS (
), MAX_DATA AS (
), MAX_DATA_FINAL AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY SUBMISSION_DATE ORDER BY TOTAL DESC, HACKER_ID ASC) AS RN FROM MAX_DATA )
SELECT A.SUBMISSION_DATE, B. TOTAL, C.HACKER_ID, C.NAME
FROM DATA_TANGGAL A LEFT JOIN COUNT_DATA B ON A.SUBMISSION_DATE = B.SUBMISSION_DATE LEFT JOIN MAX_DATA_FINAL C ON A.SUBMISSION_DATE = C.SUBMISSION_DATE WHERE RN = 1 ORDER BY A.SUBMISSION_DATE ASC
go