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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

  • sufiyan45666
     + 0 comments

    The issue with your SQL query is that it doesn't account for potential NULL values in the SUM functions. If any of the SUMs are NULL, the entire row will be excluded from the results, even if the other SUMs are greater than 0.

    To fix this, you can use the COALESCE function to replace NULL values with 0. Here's the corrected query:

    SELECT 
  contests.contest_id, 
  hacker_id, 
  name, 
  COALESCE(SUM(total_submissions), 0) AS total_submissions,
  COALESCE(SUM(total_accepted_submissions), 0) AS total_accepted_submissions,
  COALESCE(SUM(total_views), 0) AS total_views,
  COALESCE(SUM(total_unique_views), 0) AS total_unique_views
FROM 
  contests 
  JOIN colleges ON contests.contest_id = colleges.contest_id
  JOIN challenges ON colleges.college_id = challenges.college_id
  JOIN View_Stats ON challenges.challenge_id = View_Stats.challenge_id
  JOIN Submission_Stats ON challenges.challenge_id = Submission_Stats.challenge_id
GROUP BY 
  contests.contest_id, hacker_id, name
HAVING 
  COALESCE(SUM(total_submissions), 0) + 
  COALESCE(SUM(total_accepted_submissions), 0) + 
  COALESCE(SUM(total_views), 0) + 
  COALESCE(SUM(total_unique_views), 0) > 0
ORDER BY 
  contests.contest_id;

    By using COALESCE, you ensure that NULL values are treated as 0, and the query will return the expected results.