Codecademy Learners Challenge Project (SQL)

tatianebrandao · September 11, 2020, 12:06am

Love the way your thought to figure out the 4th question! I did it quite differently, as long we managed to get the same results thats what matters!

tatianebrandao · September 11, 2020, 12:13am

Hey everyone!

I’ve just managed to finish the challenge and would love to hear some feedback!

It was quite challenging, however, I feel really proud of myself for having managed to finish it!

gist.github.com

https://gist.github.com/codecademydev/bd63fc68f269ee451fd882c5f8862e4a

test.sqlite

-- QUESTION 2

SELECT *
FROM users
LIMIT 5;

SELECT *
FROM progress
LIMIT 5;

This file has been truncated. show original

tatianebrandao · September 11, 2020, 12:21am

Fair play to you! I was thinking of calculating the percentages as well but I was just overwhelmed after having managed to calculate the non null values

dev5530963037 · September 11, 2020, 1:51am

Hello

I liked your solution, can you explain me why do you use this statement != ‘’

hugolira0596889878 · September 18, 2020, 6:16pm

Hey,
Could you take a look at my solution? I would love a review. Thanks

gist.github.com

https://gist.github.com/codecademydev/e744911b0761099bb262fe7437385a7e

test.sqlite

--> Question 2.1
SELECT 
email_domain,
COUNT(*)
FROM users
WHERE email_domain LIKE '%.edu'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 25;

This file has been truncated. show original

kennyx0r · September 18, 2020, 10:34pm

Hi!

I think your last query is missing “_data” at line 85 (“from school_course_preferences”) where you select colums from your previous table “school_course_preferences_data”

Personally I ve found a little dificult to read and test everything in a single query, I would rather have many diferent querys to follow point by point.

I ll post my solution soon, hope to hear your opinion too!

kennyx0r · September 18, 2020, 10:57pm

Hi!

At line 29 I would rather use:

WHERE mobile_app IS NOT ''

Since NOT NULL is also counting the blanks or empty strings… but I guess you already figured that out

I didnt know about the IN operator and I have just learnt about it reading your code, thanks!

kennyx0r · September 18, 2020, 11:47pm

This is my solution.

github.com

martingavioli/cc-learners_mockup_data/blob/master/learners_mockup_data.sql

-- 2.A
-- top 25 schools
SELECT email_domain,
	count(*) AS students
FROM users
GROUP BY email_domain
ORDER BY 2 DESC
LIMIT 25;


-- 2.B
-- NYC edu learners
SELECT count(*)
FROM users
WHERE city = 'New York'
AND email_domain LIKE '%.edu';


-- 2.C
-- mobile users count

This file has been truncated. show original

I had to struggle a bit with the empty string values since the NOT NULL operator wasn’t working as I expected, but this tip by @stefanstratulat helped me a lot!

Looking at other solutions I think I missed the point at the 4rth question, since I sorted it by the number of courses each school was offering.

At the last 2 points I wanted to set the results in a vertical column to be able to sort them by order of preference in each city, it took me a while but I got it!

I am pretty sure there may be a better way to do it though, so… if any of you has any insight it will be very apreciated.

Thank you.

h1lo · September 19, 2020, 4:23pm

I didn’t even notice that! Thank you for pointing it out!

Hm, I didn’t know my way of querying could decrease readability. I thought splitting the data up into tables would make it easier to follow.

css2792052397 · October 2, 2020, 10:56pm

This is what I got.

gist.github.com

https://gist.github.com/codecademydev/9d4d3b5530732411279b4cfdd5c4bc6f

test.sqlite

-- For question 2, I'll get a sample of both tables to see how they are formatted
SELECT *
FROM users
LIMIT 5;

SELECT *
FROM progress
LIMIT 5;

--2.a What are the Top 25 schools (.edu domains)?

This file has been truncated. show original

board9959954527 · October 14, 2020, 7:45pm

By far the most elegant answer I’ve seen. The open/close quotation seems to account for the fact that the data type doesn’t allow for it to be “NULL”. I also used a series of CASES to identify as 1 or 0 the students taking each class. However, I did this in a separate temporary table. Not as efficient. Thanks for sharing.

BTW, for those of you copying this into the workspace, watch out for the quotations. They may need to be re entered to work correctly.

board9959954527 · October 14, 2020, 7:59pm

Here’s my solution. Most interesting part is probably the fact that I didn’t check the schema and trusted the description of the tables in the intro. Codecademy didn’t include the city as a column, so I tried to identify students in the STATE of New York by postal codes.

board9959954527 · October 14, 2020, 8:00pm

gist.github.com

https://gist.github.com/kukev/1c207446b228967ed7da706ae576eee2

learners_mockup.sqlite

---What are the top 25 student email domains?
SELECT email_domain,
  COUNT(email_domain)
FROM users
WHERE email_domain LIKE '%.edu'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 25;

---How many .edu learners are from New York(state)? (there are no email domains not like .edu FYI)

This file has been truncated. show original

sanmehra · September 6, 2021, 1:06am

Here is my solution set:

gist.github.com

https://gist.github.com/codecademydev/643c1835f7ff21070abfe50b8741b981

test.sqlite

--Top 25 schools:
select email_domain ,count(*) as Counts from users
group by email_domain
order by 2 desc
limit 25;

-- Number of NY learners
select count(*) as NY_Learners 
from users where city = 'New York';

This file has been truncated. show original

bunmialake · October 28, 2021, 6:10pm

github.com

Bumilake/git_practice/blob/5803b12ae4650b4e3f956ae92c5104501064466c/sql_data_exercise

-- Top 25 schools
select 
email_domain,
count(email_domain) as Top_25
FROM users
group by email_domain
order by Top_25 desc
limit 25;

-- Number of .edu learners in NY
select
city, 
count(*) as No
from users
where city = "New York" and email_domain like "%.edu%";

-- number of mobile users
select
count(*) as no_mobile_users
from users

This file has been truncated. show original

sadiakalam3410131967 · June 4, 2022, 8:23pm

Took me a while to work around the NULL bits which were messing up my numbers for the last q but managed to figure it out…

gist.github.com

https://gist.github.com/sadiakd/63e9c1d2c7102d4680e391087ab9c4e6

Codecademy Learners Mockup Data.sqlite

SELECT *
FROM users
LIMIT 5;

SELECT *
FROM progress
LIMIT 5;

-- Top 25 schools

This file has been truncated. show original

py4149901675 · August 8, 2022, 11:35pm

Hi guys!
I would like to share my solution for Codecademy Learners Mockup Data Project.

github.com

Lu-Yuqing/codecademy_data/blob/main/codecademy.sqlite

-- check tables

SELECT * FROM users
LIMIT 10;
SELECT * FROM progress
LIMIT 10;

-- Top 25 schools based on email domain

SELECT email_domain, COUNT(*)
FROM users
GROUP BY 1
ORDER BY 2 DESC
LIMIT 25;

-- number of .edu learners in New York

SELECT city, COUNT(*)
FROM users
WHERE city = 'New York';

This file has been truncated. show original

jblakemore19 · December 12, 2022, 9:04pm

Hey All,

I wanted to do this project off platform using DB Browser for SQLite. I downloaded both databases in the beginning, but when I tried to open them I received a pop up about “SQLCipher encryption” which requested a password/encryption key.

I’ve encountered this a couple of times before, when trying to import other public data for a project.

Did I do something wrong? Where do I go from here? I completed the project on Code Academy but am really trying to get more familiar other workspaces.

This may be a question for a different part of the forum. I’m just curious if anyone else downloaded the User and Progress tables and experienced the same roadblock.

Thanks!