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.
15 Days of Learning SQL
15 Days of Learning SQL
Sort by
recency
|
1215 Discussions
|
Please Login in order to post a comment
MS SQL SERVER
In Oracle:
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
--Using SQL Server-- declare @consistent_hackers table( hacker_id int, submission_date date );
declare @count_uniqhackers table( hacker_count int, submission_date date );
insert into @consistent_hackers select hacker_id, submission_date from submissions where submission_date like '2016-03-01' declare @submssn_date date declare @remaining_date date
set @submssn_date = '2016-03-01' set @remaining_date = '2016-03-01'
while @submssn_date < '2016-03-15' begin set @submssn_date = dateadd(day, 1, @submssn_date) insert into @consistent_hackers select s.hacker_id, s.submission_date from submissions s join @consistent_hackers c on s.hacker_id = c.hacker_id and c.submission_date like @remaining_date where s.submission_date like @submssn_date set @remaining_date = dateadd(day, 1, @remaining_date); end; insert into @count_uniqhackers select count(distinct hacker_id), submission_date from @consistent_hackers group by submission_date ;
with max_hacker as ( select row_number() over( partition by s.submission_date order by count(s.hacker_id) desc, s.hacker_id asc ) as rn, s.hacker_id, s.submission_date, name from submissions s inner join hackers h on s.hacker_id = h.hacker_id group by s.hacker_id, s.submission_date, name ) select s.submission_date, c.hacker_count, m.hacker_id, m.name from submissions s join max_hacker m on s.submission_date = m.submission_date and m.rn = 1 join @count_uniqhackers c on s.submission_date = c.submission_date --where --s.submission_date like '2016-03-01' group by s.submission_date, c.hacker_count, m.hacker_id, m.name order by s.submission_date
go