You are viewing a single comment's thread. Return to all comments →
With help of everybody on this problem discussion, I was able to carefully parse the problem requirement to determine the actual ask. The following was not obvious: 1. 'total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest)' Took a bit to realize that the ask was not to determine who submitted on all 15 days, but rather who submitted on consecutive days from day 1 up to each specific day. So, by default, everybody who submitted on day 1 was in the target group. On Day 2, you had to have also submitted on day 1. On day 3, you had to have also submitted on Day 1 and day 2, and so on. Future days were not part of this calculation. 2. 'name of the hacker who made maximum number of submissions each day' Took a bit to realize that this point had no relationship to Point 1. Just get a count of submissions by each hacker on that particular day, then pick the one with the highest count, and if there are duplicates, sort ascending by the HACKER_ID and pick the lowest valued HACKER_ID. The following works in ORACLE: ------------------------------------------------------------------------------- -- Hardwire the date criteria in one place ------------------------------------------------------------------------------- WITH dt_start AS (SELECT DATE '2016-03-01' AS dtst ,15 as dtcnt FROM DUAL) ------------------------------------------------------------------------------- -- Build a list of required dates ------------------------------------------------------------------------------- ,dts AS (SELECT dtst + level - 1 AS submission_date FROM DUAL CROSS JOIN dt_start CONNECT BY LEVEL <= dtcnt) ------------------------------------------------------------------------------- -- Count how many submissions per hacker per each relevant day ------------------------------------------------------------------------------- ,t1 AS (SELECT hacker_id ,submissions.submission_date ,COUNT(*) AS cntsub FROM submissions WHERE submissions.submission_date IN (SELECT submission_date FROM dts) GROUP BY hacker_id ,submission_date) ------------------------------------------------------------------------------- -- Get a count of number of hackers who have consecutively submitted at -- least one hack for all days prior to each individual day (and including the -- day itself). ------------------------------------------------------------------------------- ,maxhack AS (SELECT submission_date ,COUNT(DISTINCT hacker_id) AS hackercnt FROM submissions CROSS JOIN dt_start GROUP BY submission_date START WITH submission_date = dtst CONNECT BY PRIOR submission_date = submission_date - 1 AND PRIOR hacker_id = Hacker_id and submission_date <= dtst + dtcnt -1) ------------------------------------------------------------------------------- -- For every day, find teh HACKER_ID with most number of submissions. Use -- ROW_NUMBER() function to select only the first among duplicates (based on -- lowest numbered HACKER_ID). JOIN to HACKERS to get hacker NAME and to -- MAXHACK to get count of distinct hackers for each day. ------------------------------------------------------------------------------- SELECT a.submission_date ,hackercnt ,a.hacker_id, name FROM (SELECT ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY hacker_id) AS rnum ,submission_date ,hacker_id, cntsub FROM (SELECT submission_date ,hacker_id, cntsub FROM t1) a WHERE a.cntsub = (SELECT MAX(cntsub) FROM t1 WHERE t1.submission_date = a.submission_date) ) a JOIN hackers ON a.hacker_id = hackers.hacker_id JOIN maxhack ON a.submission_date = maxhack.submission_date WHERE rnum = 1 ORDER BY a.submission_date ;
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 →