Sanity check failing in the initial subquery
This is the subquery i have writen but i am getting more than 50000 resulting rows, while i should get 20,255 rows as per the project sanity check parameter. I am not understanding how to apply for rejecting null values here or do not count repeating values as i tried using Distinct with student id but still the same result. Kindly guide:
SELECT
distinct i.student_id,
date_registered,
MIN(date_watched) AS first_date_watched,
MIN(date_purchased) AS first_date_purchased,
DATEDIFF(date_watched, date_registered) AS days_diff_reg_watch,
DATEDIFF(date_purchased, date_watched) AS days_diff_watch_purch
FROM
student_engagement e
JOIN
student_info i ON
i.student_id = e.student_id
JOIN
student_purchases p ON
p.student_id = i.student_id
GROUP BY i.student_id, date_registered, days_diff_reg_watch, days_diff_watch_purch
HAVING first_date_watched <= first_date_purchased;
Hi! I had the same issue. Your solution is correct except for the last part.
The HAVING clause should include a contition about the first_date_purchased whose value is NULL. It should look like this:
having first_date_purchased is null or first_date_watched <= first_date_purchased;
That way you should get the 20,255 records. Also, by the way you have your query it should be enough to group by student_id only.
Hey Daniel thanks for the reply, Actually i did update my having clause with what you said but i am still getting more than 50k rows in the result :(
Also with student_id only in group by it was giving me an error so i included date_registered as well, still the same error then i included all that i shown which is when the error was gone.
So the problem still persists for me. Don't know how are people getting 20255 rows. I guess there is something to do with Null values and how they should be eliminated as wel as distinct values in resulting student_id column, but am not really sure how to go about it. :(
Hello Daniel and Prashant, I have been struggling to solve the project even with the guided version, I saw I got really close to what Prashant posted but I'm a little bit desperate for help, can any of you reach out to help me please? I'd greatly appreciate it
Submit an answer
