Project: Interview prep problems | Codecademy | Analyze data with SQL | 9. Interview prep | Interview Prep

interview_prep_problems.txt

-- CODE CHALLENGE 1
-- Select the title, author, and average_rating of each book with an average_rating between 3.5 and 4.5.
SELECT
  title,
  author,
  average_rating
FROM books
WHERE average_rating BETWEEN 3.5 AND 4.5;




-- Select all the unique authors from the table.
SELECT
  DISTINCT(author)
FROM books;




-- CODE CHALLENGE 2
/*
Given the final scores of several NBA games, use CASE to return the results for each game:
If home team won, return ‘HOME WIN’.
If away team won, return ‘AWAY WIN’.
Select the id column and the CASE result.
*/
SELECT id, 
  CASE
    WHEN home_points > away_points 
      THEN 'HOME WIN'
    ELSE 'AWAY WIN'
  END AS 'Case'
FROM nba_matches;




-- CODE CHALLENGE 3
-- Find the number of apps by genre.
SELECT genre,
  COUNT(*)
FROM apps
GROUP BY genre;




/*
Get the total number of reviews of all apps by genre.
Limit the results for genres where the total number of app reviews is over 30 million.
*/
SELECT genre,
  SUM(reviews) AS review_total
FROM apps
GROUP BY genre
HAVing sum(reviews) > 30000000;




-- CODE CHALLENGE 4
-- Select the name, genre, and rating of apps in descending order of their rating, and limit the result to 20 rows.
SELECT
  name,
  genre,
  rating
FROM apps
ORDER BY rating DESC
LIMIT 20;




-- CODE CHALLENGE 5
-- Find the lowest and highest rating for all apps using two different queries.
SELECT
  MIN(rating)
FROM apps;

SELECT
  MAX(rating)
FROM apps;




-- Get the average rating of all apps, rounded to 2 decimal places. Alias the result column as ‘average rating’.
SELECT
  ROUND(AVG(rating),2) AS 'average rating'
FROM apps;




-- CODE CHALLENGE 6
/*
The projects table stores the employee_id for the employee assigned to each project.
Perform an inner join on the two tables, matching on the primary key and foreign key described above, and select all columns.
*/
SELECT *
FROM projects
INNER JOIN employees
  ON projects.employee_ID = employees.id;




-- Perform a join between the two tables, such that it selects all projects even if there is no employee assigned to it.
SELECT *
FROM projects
LEFT JOIN employees
  ON projects.employee_ID = employees.id;
  
  
  
  
-- CODE CHALLENGE 7
-- Using a subquery, get all students in math who are also enrolled in english.
SELECT *
FROM math_students AS m
WHERE m.student_ID IN
  (
    SELECT e.student_ID
    FROM english_students AS e
  );




-- Using a subquery, find out which students in math are in the same grade level as the student with id 7.
SELECT *
FROM math_students
WHERE grade IN
  (
    SELECT grade
    FROM math_students
    WHERE student_ID = 7
  );




-- CODE CHALLENGE 8
-- Using a subquery, find all students enrolled in english class who are not also enrolled in math class.
SELECT *
FROM english_students AS e
WHERE e.student_ID NOT IN
  (
    SELECT m.student_ID
    FROM math_students AS m
  );
  
  
  

-- Using a subquery, find out what grade levels are represented in both the math and english classes.
SELECT grade
FROM math_students
WHERE EXISTS (
  SELECT grade
  FROM english_students
);




-- CODE CHALLENGE 9
/*
Using a window function with PARTITION BY, get the total change in gross for each movie up to the current week
and display it next to the current week column along with the title, week, and gross columns.
*/
SELECT title,
  week,
  gross,
  SUM(gross) OVER
  (
    PARTITION BY title
    ORDER BY week
  ) AS 'running_total_gross'
FROM box_office;




-- CODE CHALLENGE 10
-- Write a query using a window function with ROW_NUMBER and ORDER BY to see where each row falls in the amount of gross.
SELECT
  ROW_NUMBER() OVER
  (
    ORDER BY gross
  ) AS rownum,
  title, week, gross
FROM box_office;





-- CODE CHALLENGE 11
-- Given an orders table, calculate the price times quantity of each order. Include the id and product_id columns in the result.
SELECT
  ID,
  product_ID,
  price*quantity AS total
FROM orders;




-- CODE CHALLENGE 12
/*
Utilize CAST to calculate the average of the low and high temperatures for each date such that the result is of type REAL.
Select the date column and alias this result column as ‘average’.
*/
SELECT
  date,
  (CAST(low AS REAL) + CAST(high AS REAL))/2
    AS average
FROM weather
GROUP BY date;




-- CODE CHALLENGE 13
/*
After a purchase is created, it can be returned within 7 days for a full refund.
Using modifiers, get the date of each purchase offset by 7 days in the future.
*/
SELECT
  purchase_ID,
  DATE(purchase_date, '+7 days') AS return_date
FROM purchases;




-- Get the hour that each purchase was made. Which hour had the most purchases made?
SELECT
  STRFTIME('%H', purchase_date) AS purchase_hour,
  COUNT(STRFTIME('%H', purchase_date)) AS count
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;




-- CODE CHALLENGE 14
-- Using string formatting and substitutions, get the month and day for each purchase in the form ‘mm-dd’.
-- Give this new column a name of ‘reformatted’.
SELECT
  STRFTIME('%m-%d', purchase_date) as reformatted
FROM purchases;