Goal: Calculates the difference between the highest salaries in the marketing and engineering departments. Output just the absolute difference in salaries.
-- Dialect: MySQL
SELECT
ABS(m.salary_marketing - e.salary_engineering) AS salary_difference
FROM
(
SELECT MAX(emp.salary) AS salary_marketing
FROM db_employee emp
JOIN db_dept dep
ON emp.department_id = dep.id
WHERE dep.department = 'Marketing'
) m,
(
SELECT MAX(emp.salary) AS salary_engineering
FROM db_employee emp
JOIN db_dept dep
ON emp.department_id = dep.id
WHERE dep.department = 'Engineering'
) e;Goal: We have a table with employees and their salaries, however, some of the records are old and contain outdated salary information. Find the current salary of each employee assuming that salaries increase each year. Output their id, first name, last name, department ID, and current salary. Order your list by employee ID in ascending order.
-- Dialect: MySQL
SELECT
t.id,
t.first_name,
t.last_name,
t.department_id,
t.salary AS current_salary
FROM (
SELECT
e.*,
ROW_NUMBER() OVER (
PARTITION BY e.id
ORDER BY e.salary DESC
) AS rn
FROM ms_employee_salary e
) t
WHERE rn = 1
ORDER BY id;Goal: Find the job titles of the employees with the highest salary. If multiple employees have the same highest salary, include the job titles for all such employees.
-- Dialect: MySQL
SELECT b.worker_title AS best_paid_title
FROM worker a
JOIN title b ON a.worker_id = b.worker_ref_id
WHERE a.salary = (
SELECT MAX(w.salary)
FROM worker w
JOIN title t ON w.worker_id = t.worker_ref_id
)
ORDER BY best_paid_title;Goal: Find the last time each bike was in use. Output both the bike number and the date-timestamp of the bike's last use (i.e., the date-time the bike was returned). Order the results by bikes that were most recently used.
-- Dialect: MySQL
SELECT t.bike_number,
t.end_time AS most_recent_time
FROM (
SELECT bike_number,
end_time,
ROW_NUMBER() OVER(
PARTITION BY dc_bikeshare_q1_2012.bike_number
ORDER BY end_time DESC
) AS rn
FROM dc_bikeshare_q1_2012
) AS t
WHERE rn=1
ORDER BY t.end_time DESC;Goal: Compare each employee's salary with the average salary of the corresponding department. Output the department, first name, and salary of employees along with the average salary of that department.
-- Dialect: MySQL
SELECT e.first_name,
e.department,
e.salary,
AVG(e.salary) OVER (PARTITION BY e.department) AS average_salary
FROM employee e
ORDER BY e.department;Goal: Find the details of each customer regardless of whether the customer made an order. Output the customer's first name, last name, and the city along with the order details. Sort records based on the customer's first name and the order details in ascending order.
-- Dialect: MySQL
SELECT c.first_name,
c.last_name,
c.city,
o.order_details
FROM customers c
LEFT JOIN orders o ON c.id=o.cust_id
ORDER BY c.first_name ASC, o.order_details ASC;Goal: Find the number of workers by department who joined on or after April 1, 2014. Output the department name along with the corresponding number of workers. Sort the results based on the number of workers in descending order.
-- Dialect: MySQL
SELECT department,
COUNT(worker_id) AS Number_of_Workers
FROM worker
WHERE joining_date>='2014-04-01'
GROUP BY department
ORDER BY Number_of_Workers DESC;Goal: Write a query that returns the number of unique users per client for each month. Assume all events occur within the same year, so only month needs to be in the output as a number from 1 to 12.
-- Dialect: MySQL
SELECT client_id,
COUNT(DISTINCT(user_id)) AS Number_of_Customers,
MONTH(time_id) AS Month_number
FROM fact_events
GROUP BY client_id, Month_number
ORDER BY client_id;Goal: Find the average number of bathrooms and bedrooms for each city’s property types. Output the result along with the city name and the property type.
-- Dialect: MySQL
SELECT a.city,
a.property_type,
AVG(a.bathrooms) AS bathrooms_average,
AVG(a.bedrooms) AS bedrooms_average
FROM airbnb_search_details a
GROUP BY a.property_type, a.city;Goal: Find songs that have ranked in the top position. Output the track name and the number of times it ranked at the top. Sort your records by the number of times the song was in the top position in descending order.
-- Dialect: MySQL
SELECT trackname,
COUNT(trackname) AS Times_Top
FROM spotify_worldwide_daily_song_ranking
WHERE position=1
GROUP BY trackname
ORDER BY Times_Top DESC;Goal: Find how many times each artist appeared on the Spotify ranking list. Output the artist name along with the corresponding number of occurrences. Order records by the number of occurrences in descending order.
-- Dialect: MySQL
SELECT artist,
COUNT(id) AS Times_Appeared
FROM spotify_worldwide_daily_song_ranking
GROUP BY artist
ORDER BY Times_Appeared DESC;Goal: Find all Lyft drivers who earn either equal to or less than 30k USD or equal to or more than 70k USD. Output all details related to retrieved records.
-- Dialect: MySQL
SELECT *
FROM lyft_drivers
WHERE yearly_salary<=30000 OR yearly_salary>=70000;Goal: Meta/Facebook has developed a new programing language called Hack. To measure the popularity of Hack they ran a survey with their employees. The survey included data on previous programing familiarity as well as the number of years of experience, age, gender and most importantly satisfaction with Hack. Due to an error location data was not collected, but your supervisor demands a report showing average popularity of Hack by office location. Luckily the user IDs of employees completing the surveys were stored. Based on the above, find the average popularity of the Hack per office location. Output the location along with the average popularity.
-- Dialect: MySQL
SELECT e.location,
AVG(s.popularity) AS Popularyty_Location
FROM facebook_employees e
JOIN facebook_hack_survey s ON e.id=s.employee_id
GROUP BY e.location;Goal: Find order details made by Jill and Eva. Consider the Jill and Eva as first names of customers. Output the order date, details and cost along with the first name. Order records based on the customer id in ascending order.
-- Dialect: MySQL
SELECT o.order_date,
o.order_details,
o.total_order_cost,
c.first_name
FROM customers c
JOIN orders o ON c.id=o.cust_id
WHERE c.first_name IN ('Jill','Eva')
ORDER BY c.id;Goal: Write a query that will calculate the number of shipments per month. The unique key for one shipment is a combination of shipment_id and sub_id. Output the year_month in format YYYY-MM and the number of shipments in that month.
-- Dialect: MySQL
SELECT CONCAT((shipment_date), '-', MONTH(shipment_date)) AS year_month
FROM amazon_shipment;Goal: Count the number of user events performed by MacBookPro users. Output the result along with the event name. Sort the result based on the event count in the descending order.
-- Dialect: MySQL
SELECT p.event_name,
COUNT(p.user_id) AS event_count
FROM playbook_events p
WHERE p.device = 'macbook pro'
GROUP BY p.event_name
ORDER BY event_count DESC;Goal: Find the most profitable company from the financial sector. Output the result along with the continent.
-- Dialect: MySQL
SELECT
n.continent,
n.company
FROM (
SELECT *
FROM forbes_global_2010_2014
ORDER BY profits DESC
) n
LIMIT 1;Goal: Find the inspection date and risk category (pe_description) of facilities named 'STREET CHURROS' that received a score below 95.
-- Dialect: MySQL
SELECT activity_date,
pe_description
FROM los_angeles_restaurant_health_inspections
WHERE facility_name = 'STREET CHURROS'
AND score < 95;Goal: Count the total number of violations that occurred at 'Roxanne Cafe' for each year, based on the inspection date. Output the year and the corresponding number of violations in ascending order of the year.
-- Dialect: MySQL
SELECT YEAR(inspection_date) AS year,
COUNT(violation_id) AS number_of_violations
FROM sf_restaurant_health_violations
WHERE violation_id IS NOT NULL
AND business_name = 'Roxanne Cafe'
GROUP BY YEAR(inspection_date)
ORDER BY year;Goal: Find the number of employees working in the Admin department that joined in April or later, in any year.
-- Dialect: MySQL
SELECT COUNT(worker_id) AS num_admin_employees
FROM worker
WHERE department = 'Admin'
AND MONTH(joining_date) >= 4;