15 Nov 2023

Posted on:

12 Nov 2023

0

# Wrong solution: Got 64,458 rows instead of 81,532 rows

****Retrieving Courses Information with SQL****

WITH title_total_minutes AS
(
SELECT
course_id,
course_title,
ROUND(SUM(minutes_watched), 2) AS total_minutes_watched,
COUNT(DISTINCT student_id) AS num_students
FROM
365_course_info
JOIN
365_student_learning USING (course_id)
GROUP BY course_id
),

title_average_minutes AS
(
SELECT
course_id,
course_title,
total_minutes_watched,
ROUND(total_minutes_watched/num_students, 2) AS average_minutes
FROM
title_total_minutes
),

title_ratings AS
(
SELECT
course_id,
course_title,
total_minutes_watched,
average_minutes,
COUNT(course_rating) AS number_of_ratings,
ROUND(SUM(course_rating)/COUNT(course_rating), 2) AS average_rating
FROM
title_average_minutes
LEFT JOIN
365_course_ratings USING (course_id)
GROUP BY course_id
)

SELECT
course_id,
course_title,
total_minutes_watched,
average_minutes,
number_of_ratings,
average_rating
FROM
title_ratings;

****Retrieving Purchases Information with SQL****

DROP VIEW IF EXISTS purchases_info;

CREATE VIEW purchases_info AS
SELECT
purchase_id,
student_id,
purchase_type,
date_purchased AS date_start,
CASE
WHEN purchase_type = 'Monthly' THEN
INTERVAL MONTH(date_purchased) MONTH)
WHEN purchase_type = 'Quarterly' THEN
INTERVAL MONTH(date_purchased)+2 MONTH)
WHEN purchase_type = 'Annual' THEN
INTERVAL MONTH(date_purchased)+11 MONTH)
END AS date_end
FROM
365_student_purchases;

****Retrieving Students Information with SQL****

SELECT
student_id,
student_country,
date_registered,
date_watched,
minutes_watched,
onboarded,
MAX(paid) AS paid
FROM
(SELECT
student_id,
student_country,
date_registered,
date_watched,
minutes_watched,
onboarded,
IF(date_watched BETWEEN p.date_start AND p.date_end, 1, 0) AS paid
FROM
(SELECT
i.student_id,
i.student_country,
i.date_registered,
l.date_watched,
IF(l.date_watched IS NULL, 0, ROUND(SUM(l.minutes_watched), 2)) AS minutes_watched,
IF(l.date_watched IS NULL, 0, 1) AS onboarded

FROM
365_student_info i
JOIN
365_student_learning l USING (student_id)
GROUP BY student_id, date_watched) a
LEFT JOIN
purchases_info p USING (student_id)) b
GROUP BY student_id, date_watched;

4 answers ( 0 marked as helpful)
Instructor
Posted on:

15 Nov 2023

0

Hey Srujana,

Thanks for reaching out.

Could you please specify your question and the query your question is referring to? Thank you.

Kind regards,

365 Hristina

Posted on:

15 Nov 2023

0

Hi Hristina,

The above is the code I wrote for the 'Customer Engagement Analysis with SQL and Tableau Project'. So this is for Section 11 of the course, which includes the final course project. I've done the first four sections in the project, which involves writing the SQL code to retrieve the necessary data. However, according to the project, I am supposed to end up with 81,532 rows, whereas I am only getting 64,458 rows. Could you please check my code to see where I am making a mistake? Thanks a lot!

Srujana

Instructor
Posted on:

15 Nov 2023

0

Hey again Srujana,

Thank you for clarifying.

Pay close attention to the first instruction in the "Retrieving Students Information with SQL" part:

Create a query that appropriately joins the `365_student_info` and the `365_student_learning` tables, retrieving all records from the first table

Think about which `JOIN` clause needs to be used in order to retrieve all records from the `365_student_info` table.

Let me know if you need further assistance.

Kind regards,

365 Hristina

Posted on:

15 Nov 2023

1

Hi Hristina!

Thank you so much! I was able to fix my code and get the appropriate number of rows now! Thank you for helping me!

Kind regards,
Srujana