Last answered:

23 Jan 2024

Posted on:

04 Dec 2023

1

Resolved: Claculating free-to-paid conversion project

Hi! I have been working on this project for a couple of days but haven't figured out the first part "Create the subquery". It says that the number of records in the resulting set should be 20,255, but no matter how many times I rewrite the query I still get 52,142.

The code I am using is the following

SELECT
    engage.student_id AS student_id,
    info.date_registered AS date_registered,
    min(engage.date_watched) AS first_date_watched,
    min(purchase.date_purchased) AS first_date_purchased,
    DATEDIFF(engage.date_watched, info.date_registered) AS date_diff_reg_watch,
    DATEDIFF(purchase.date_purchased, engage.date_watched) AS date_diff_watch_purch
FROM
    student_engagement engage
        INNER JOIN
    student_info info ON engage.student_id = info.student_id
        INNER JOIN
    student_purchases purchase ON engage.student_id = purchase.student_id
GROUP BY student_id,date_registered,date_diff_reg_watch,date_diff_watch_purch
HAVING first_date_purchased >= first_date_watched

I changed to the Guided track of the project to check if the query was correct and at least the statements were in the right order, but I'm still confused about why I got 52,142 records.

I truly appreciate any guidance in the right direction. Thank you very much!

2 answers ( 1 marked as helpful)
Posted on:

13 Dec 2023

1

Hi!


I have the similar problem, when first I used one condition with the clause HAVING (first_date_watched <= first_date_purchased), I got 2286 records because not count the null values. Then I used two conditions with the clause HAVING, I got the correct value (20,255).


Thanks!

Posted on:

23 Jan 2024

0

Hi, i'm trying to create Database in MS SQL SERVER but the Database script is giving errors.

Can Someone help me to solve this?

Submit an answer