44. Number of Comments per Post
Table: Submissions
+---------------+----------+
| Column Name | Type |
+---------------+----------+
| sub_id | int |
| parent_id | int |
+---------------+----------+
There is no primary key for this table, it may have duplicate rows. Each row can be a post or comment on the post. parent_id is null for posts. parent_id for comments is sub_id for another post in the table.
Write an SQL query to find number of comments per each post.
Result table should contain post_id and its corresponding number_of_comments, and must be sorted by post_id in ascending order. Submissions may contain duplicate comments. You should count the number of unique comments per post. Submissions may contain duplicate posts. You should treat them as one post.
SELECT a.sub_id AS post_id, COUNT(DISTINCT b.sub_id) AS number_of_comments
FROM
(SELECT sub_id
FROM submissions
WHERE parent_id IS NULL) AS a
LEFT JOIN submissions AS b
ON a.sub_id = b.parent_id
GROUP BY a.sub_id
;
在join的时候为了确保信息的全面，最好避免用inner join。
45. Queries Quality and Percentage
Table: Queries
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
There is no primary key for this table, it may have duplicate rows.This table contains information collected from some queries on a database. The position column has a value from1 to 500.The rating column has a value from1to5.
We define query quality as:
The average of the ratio between query rating and its position.
We also define poor query percentage as:
The percentage of all queries with rating less than 3.
Write an SQL query to find each query_name, the quality and poor_query_percentage.
Both quality and poor_query_percentage should be rounded to 2 decimal places.
SELECT query_name,
ROUND(IFNULL(AVG(rating/position), 0), 2) AS quality,
ROUND(IFNULL(COUNT(CASE WHEN rating < 3 THEN rating END)*100/COUNT(rating), 0), 2) AS poor_query_percentage
FROM queries
GROUP BY query_name
;
46. Article Views I
Table: Views
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date. Note that equal author_id and viewer_id indicate the same person.
Write an SQL query to find all the authors that viewed at least one of their own articles, sorted in ascending order by their id.
SELECT author_id AS id
FROM views
WHERE author_id = viewer_id
GROUP BY author_id
HAVING COUNT(viewer_id) >= 1
ORDER BY id
;
一定注意加order by，再次忘记。
47. User Activity for the Past 30 Days I
Table: Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.
Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on some day if he/she made at least one activity on that day.
SELECT activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity_date BETWEEN ADDDATE('2019-07-27', INTERVAL -29 DAY) AND '2019-07-27'
GROUP BY activity_date
;
48. Reported Posts
Table: Actions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| post_id | int |
| action_date | date |
| action | enum |
| extra | varchar |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The action column is an ENUM type of ('view', 'like', 'reaction', 'comment', 'report', 'share').
The extra column has optional information about the action such as a reason for report or a type of reaction.
Write an SQL query that reports the number of posts reported yesterday for each report reason. Assume today is 2019-07-05.
SELECT extra AS report_reason, COUNT(DISTINCT post_id) AS report_count
FROM actions
WHERE action_date = '2019-07-04' AND extra = 'report'
GROUP BY extra
ORDER BY extra
;
49. Game Play Analysis I
Table:Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.
Write an SQL query that reports the first login date for each player.
SELECT player_id, MIN(event_date) AS first_login
FROM activity
GROUP BY player_id
;
50. Sales Analysis I
Table:Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id is the primary key of this table.
Table:Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
This table has no primary key, it can have repeated rows.
product_id is a foreign key to Product table.
Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.
SELECT seller_id
FROM sales
GROUP BY seller_id
HAVING SUM(price) = (SELECT SUM(price) AS tol_p FROM sales GROUP BY seller_id ORDER BY tol_p DESC LIMIT 1);
51. Project Employees II
Table:Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.employee_id is a foreign key toEmployeetable.
Table:Employee
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id is the primary key of this table.
Write an SQL query that reports all the projects that have the most employees.
SELECT project_id
FROM project
GROUP BY project_id
HAVING COUNT(employee_id) = (SELECT COUNT(employee_id) AS num FROM project GROUP BY project_id ORDER BY num DESC LIMIT 1)
;
