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
|
1193 Discussions
|
Please Login in order to post a comment
Explanation: Now, the code below might seem daunting that's why it took me more than a day to figure out with a little help from various advance chat bots; more often than giving wrong response. The real challenge was to figure out the number of coders who have submitted at leat one submission from the start date to the current date, which is not mentioned clearly in the question itself, without the use of CTEs and Window Functions since my MySQL version was not allowing it Being from Statistics background and having has studied Probability theory, I can see that the question is ambiguous in its statement about the requirement for the first column.
MySQL Code:
Took a long time after missing a point in the question. doesn't seem like there's any way to do this without a temp table, or CTE
Hi, where's my problem?
select fecha, trabajos, numero, a.name from ( select fecha, max(trabajos) trabajos, min(numero) numero from ( select a.submission_date fecha, count(a.submission_date) trabajos, a.hacker_id numero from ( SELECT hacker_id id, COUNT(DISTINCT submission_date) AS total FROM Submissions GROUP BY hacker_id HAVING COUNT(DISTINCT submission_date) = (SELECT COUNT(DISTINCT submission_date) FROM Submissions) ) temp inner join Submissions a on a.hacker_id = temp.id where temp.total = (select count(distinct submission_date) from Submissions) group by a.hacker_id, a.submission_date ) temp2 group by fecha ) temp3 inner join Hackers a on a.hacker_id = temp3.numero order by fecha;
with daily_count as ( select s.submission_date,s.hacker_id,h.name,count(s.submission_id) as dail_cna from submissions s left join hackers h on s.hacker_id=h.hacker_id group by s.submission_date,s.hacker_id,h.name ), max_daily as ( select submission_date,hacker_id,name, row_number() over(partition by submission_date order by dail_cna desc,hacker_id) as rna from daily_count ), fin as ( select hacker_id,submission_date, dense_rank() over(partition by hacker_id order by submission_date ) as sina from submissions group by hacker_id,submission_date ), fina1 as ( select submission_date,count(hacker_id) as bbc from fin where day(submission_date)=sina group by submission_date ) select distinct m.submission_date,f.bbc,m.hacker_id,m.name from max_daily m left join fina1 f on m.submission_date=f.submission_date where rna=1;
CAN ANYONE HELP WHY THIS DOESN'T WOTK
with cte as (select submission_date,count(distinct hacker_id)as cnt from Submissions group by submission_date) , cte2 as (select submission_date , hacker_id , count()as sub_cnt , rank() over(partition by submission_date order by count()desc ,hacker_id asc)as rnk from Submissions group by hacker_id,submission_date) select cte.submission_date , cte.cnt , cte2.hacker_id,h.name from cte2 join Hackers h on h.hacker_id=cte2.hacker_id join cte on cte2.submission_date=cte.submission_date
can anyone
where cte2.rnk=1 order by submission_date;