Codecademy export

test.sqlite

with codecademy_data as (
  select *
  from users
  join progress
  on users.user_id = progress.user_id
),
top_25_schools as (
  select email_domain, 
       count(*) as count
  from codecademy_data
  group by 1
  order by 2 desc
  limit 25
),
edu_New_York as (
  select count(*) as 'New_York_learners'
  from codecademy_data 
  where city is 'New York'
),
mobile_users as (
  select count(*) as 'mobile_users'
  from codecademy_data
  where mobile_app is 'mobile-user'
),
sign_up_per_hour as (
  select count(sign_up_at) as count,
      strftime('%H', sign_up_at) as hour
  from users
  group by hour
  order by 2 asc
),
cpp as (
SELECT email_domain, COUNT(*) AS cpp
FROM codecademy_data
WHERE NOT learn_cpp = ''
GROUP BY 1
order by 2 desc
),
sql as (
SELECT email_domain, COUNT(*) AS sql
FROM codecademy_data
WHERE NOT learn_sql = ''
GROUP BY 1
order by 2 desc
),
html as (
SELECT email_domain, COUNT(*) AS html
FROM codecademy_data
WHERE NOT learn_html = ''
GROUP BY 1
order by 2 desc
),
javascript as (
SELECT email_domain, COUNT(*) AS javascript
FROM codecademy_data
WHERE NOT learn_javascript = ''
GROUP BY 1
order by 2 desc
),
java as (
SELECT email_domain, COUNT(*) AS java
FROM codecademy_data
WHERE NOT learn_java = ''
GROUP BY 1
order by 2 desc
), 
school_course_preferences_data as (
  select * from cpp
  join sql 
    on cpp.email_domain = sql.email_domain
  join html
    on sql.email_domain = html.email_domain
  join javascript
    on html.email_domain = javascript.email_domain
  join java
    on javascript.email_domain = java.email_domain
),
scpd_query_format as (
select email_domain,
  cpp,  
  sql,
  html,
  javascript,
  java
from school_course_preferences 
limit 10
)
select "This is the end of my queries!" as end
;