Codecademy Learners Challenge Project (SQL)

Congratulations on completing your project!

Compare your project to our solution code and share your project below! Your solution might not look exactly like ours, and that’s okay! The most important thing right now is to get your code working as it should (you can always refactor more later). There are multiple ways to complete these projects and you should exercise your creative abilities in doing so.

This is a safe space for you to ask questions about any sample solution code and share your work with others! Simply reply to this thread to get the conversation started. Feedback is a vital component in getting better with coding and all ability levels are welcome here, so don’t be shy!

About community guidelines: This is a supportive and kind community of people learning and developing their skills. All comments here are expected to keep to our community guidelines


How do I share my own solutions?

  • If you completed the project off-platform, you can upload your project to your own GitHub and share the public link on the relevant project topic.
  • If you completed the project in the Codecademy learning environment, use the share code link at the bottom of your code editor to create a gist, and then share that link here.

Do I really need to get set up on GitHub?
Yes! Both of these sharing methods require you to get set up on GitHub, and trust us, it’s worth your time. Here’s why:

  1. Once you have your project in GitHub, you’ll be able to share proof of your work with potential employers, and link out to it on your CV.
  2. It’s a great opportunity to get your feet wet using a development tool that tech workers use on the job, every day.

Not sure how to get started? We’ve got you covered - read this article for the easiest way to get set up on GitHub.

Best practices for asking questions about the sample solution

  • Be specific! Reference exact line numbers and syntax so others are able to identify the area of the code you have questions about.
1 Like

Can anyone review my solution for the last few questions?

–Do different schools (.edu domains) prefer different courses?
SELECT u.email_domain, p.learn_cpp, p.learn_sql, p.learn_html, p.learn_javascript, p.learn_java
FROM users u
LEFT JOIN progress p
ON u.user_id = p.user_id
GROUP BY 1
ORDER BY 1
LIMIT 10;

–What courses are the New Yorkers students taking?
SELECT *
FROM users u
LEFT JOIN progress p
ON u.user_id = p.user_id
WHERE city = ‘New York’;

–What courses are the Chicago students taking?
SELECT *
FROM users u
LEFT JOIN progress p
ON u.user_id = p.user_id
WHERE city = ‘Chicago’;

Hello, I hope you’re ok.
I worked in Calculating Churn Rates Project, I attached the link. Who wants to check it, please feel free to do it (You’ll find the code and the presentation of results). I will appreciate all kind of help.

Thank you.

I cannot open the solution file it’s encrypt files, can anyone share the pw?

2 Likes

My solution; I struggled to get the NULL value logic working and so resorted to what is possibly not the most elegant solution - BUT it works afaik and displays the data neatly.

I had the same issue with NULL values. I couldn’t manage to figure it out why doesn’t show properly the data.

2 Likes

Found the answer on why NULL doesn’t work: When storing missing data, should I store them as NULL?
Because the column isn’t made to store NULL values;

1 Like

Here is my humble way for this solve. I’ve managed to get over the NULL issue mentioned here after some google search. Hope my code will bring some insights to my fellow learners

Hi! I want to share how I went over the NULL value behaviour in this project…

I downloaded the files to work on DB Browser for SQLite. While working with it, I encountered the same problem associated to the NULL values that you have been all facing. I do believe that this is happening because the fields are not NULL, but “” or something similar. While looking for a solution, I observed that the downloaded files included the users and progress tables as separated CSV files, which got me thinking…

I started from scratch, opening a new project in DB Browser for SQLite. I created a new blank database, imported the two CSV files containing the users and progress tables (prior to that, I included a first line with the names of the columns, as the provided CSV files lack that information - you can do that with any text editor) and changed the field types to the proper setting (as by default DB Browser imports them as TEXT). I tested it and surprise! The NULL values were behaving as expected, with COUNT working properly (i.e. counting only NOT NULL values). For instance, the following code…

SELECT COUNT(mobile_app) AS 'mobile_app_users'
FROM users;

…returned a value of 330 instead of 2000.

Hope it helps! Please, feel free to ask if you have any doubt…

2 Likes

Hi. Just finished ‘‘Codecademy Learners mockup Data’’, and I want to compare my answers with your solutions. Can’t find which file in solution folder is related to solutions?

2 Likes

I just used the following code, I had already joined the tables in a new table called join_table. Got the same answer of 330

SELECT "Codecademy mobile app users : " + COUNT(mobile_app)
FROM join_table
WHERE mobile_app = “mobile-user”;

Thanks for your reply… Yeah, I guess that by using the WHERE clause you are filtering out the problematic NULL (or not-NULL) records…

My solution is as below.

I worked out the percentage of students who signed up for the course in each school to see the most popular then grouped the volumes of question 2 / 3 together in one query.

–Task 4

–Do different schools (.edu_domains) prefer different courses

with summary as

(

select

users.email_domain as ‘School’,

count(*) as NoStudents,

sum(case when progress.learn_cpp != ‘’ then 1 else 0 end) as LearnCppCount,

sum(case when progress.learn_sql != ‘’ then 1 else 0 end) as LearnSqlCount,

sum(case when progress.learn_html != ‘’ then 1 else 0 end) as LearnHtmlCount,

sum(case when progress.learn_javascript != ‘’ then 1 else 0 end) as LearnJavascriptCount,

sum(case when progress.learn_java != ‘’ then 1 else 0 end) as LearnJavaCount

from users join progress on users.user_id = progress.user_id

group by users.email_domain

order by 2 desc

)

select

School,

round(((1.0 * LearnCppCount / NoStudents)*100),2) as PercentCpp,

round(((1.0 * LearnSqlCount / NoStudents)*100),2) as PercentSQL,

round(((1.0 * LearnHtmlCount / NoStudents)*100),2) as PercentHTML,

round(((1.0 * LearnJavascriptCount / NoStudents)*100),2) as PercentJavascript,

round(((1.0 * LearnJavaCount / NoStudents)*100),2) as PercentHJava

from summary;

–What courses are New York and Chicago students taking?

Select

city,

sum(case when progress.learn_cpp != ‘’ then 1 else 0 end) as LearnCppCount,

sum(case when progress.learn_sql != ‘’ then 1 else 0 end) as LearnSqlCount,

sum(case when progress.learn_html != ‘’ then 1 else 0 end) as LearnHtmlCount,

sum(case when progress.learn_javascript != ‘’ then 1 else 0 end) as LearnJavascriptCount,

sum(case when progress.learn_java != ‘’ then 1 else 0 end) as LearnJavaCount

from users join progress on users.user_id = progress.user_id

where city in (‘New York’,‘Chicago’)

group by 1;

My addition to the solution.
The NULL-problem has been described in various posts. I chose to do a WHERE-clause as WHERE learn_html = ‘completed’ OR learn_html = 'started’
I’ve seen more smooth solutions, but it does the same job.

/*

–Top 25 schools (.edu domains)

SELECT email_domain, COUNT(user_id) ‘Learners’

FROM users

GROUP BY email_domain

ORDER BY Learners DESC

LIMIT 25;

–no of .edu learners in NY

SELECT COUNT(*) FROM users

WHERE city = ‘New York’ AND email_domain LIKE ‘%.edu%’;

–no of learners using mobile app

SELECT mobile_app, COUNT(*) FROM users

GROUP BY 1;

–percentages

SELECT mobile_app,

100 * COUNT(mobile_app) / (SELECT COUNT(*) FROM users) ‘%’

FROM USERS

GROUP BY 1;

–hour of sign-up

SELECT COUNT() antal, strftime(’%H’, sign_up_at) ‘Sign-up hour’, (SELECT COUNT() FROM users)

FROM users

GROUP BY 2

ORDER BY 1 DESC

LIMIT 20;

–joining the tables

WITH cpp AS (

SELECT u.email_domain, COUNT(*) AS cpp  

FROM users u

JOIN progress p

ON u.user_id = p.user_id

WHERE learn_cpp = 'completed' OR learn_cpp = 'started'

GROUP BY 1

),

sql AS (

SELECT u.email_domain, COUNT(*) AS sql  

FROM users u

JOIN progress p

ON u.user_id = p.user_id

WHERE learn_sql = 'completed' OR learn_sql = 'started'

GROUP BY 1

),

html AS (

SELECT u.email_domain, COUNT(*) AS html  

FROM users u

JOIN progress p

ON u.user_id = p.user_id

WHERE learn_html = 'completed' OR learn_html = 'started'

GROUP BY 1

),

javascript AS (

SELECT u.email_domain, COUNT(*) AS javascript  

FROM users u

JOIN progress p

ON u.user_id = p.user_id

WHERE learn_javascript = 'completed' OR learn_javascript = 'started'

GROUP BY 1

),

java AS (

SELECT u.email_domain, COUNT(*) AS java  

FROM users u

JOIN progress p

ON u.user_id = p.user_id

WHERE learn_java = 'completed' OR learn_java = 'started'

GROUP BY 1

)

SELECT u.email_domain,

    u.country,

    u.city,

    cpp.cpp,

    sql.sql,

    html.html,

    javascript.javascript,

    java.java

FROM users u

LEFT JOIN cpp ON u.email_domain = cpp.email_domain

LEFT JOIN sql ON u.email_domain = sql.email_domain

LEFT JOIN html ON u.email_domain = html.email_domain

LEFT JOIN javascript ON u.email_domain = javascript.email_domain

LEFT JOIN java ON u.email_domain = java.email_domain

–WHERE u.city = ‘New York’

WHERE u.city = ‘Chicago’

GROUP BY 1

ORDER BY 4 DESC

LIMIT 50;

*/

Here’s my solution. Please review

Hello! I will share my answers below, on this GitHub link. It would mean a lot to me if anyone could see how I answered the last questions!

1 Like

Hi all!

Here are my answers for the project, please feel free to give any feedback regarding my answers! Thank you :slight_smile:

hi everyone!!
I worked it on DB Browser

This is just addressing the question: ‘Do different schools prefer different courses?’ As I couldn’t get IS NOT NULL to work in the CASE statements, I decided to check for either of the two answers given (‘completed’ or ‘started’). It works! But it’s not very versatile without checking for NULL.

1 Like

My project!