7题属于delete题型，很少用到，先跳过。
8. Rising Temperature
id is the primary key for this table.
This table contains information about the temperature in a certain day.
Table: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday). Return the result table in any order.
SELECT b.id
FROM weather AS a
INNER JOIN weather AS b
ON DATEDIFF(b.recorddate, a.recorddate) = 1
WHERE b.temperature > a.temperature
;
这道题不难，但是datediff有几个需要注意的地方，datediff很容易写成date_diff，这是不对的。还有就是datdiff（x，y）= z，是x-y=z，不是y-x=z。还有就是datediff不管是用月份年份还是天数相减，最后得到的都是天数。
9. User Activity for the Past 30 Days II
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.
The query result format is in the following example:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 3 | 5 | 2019-07-21 | open_session |
| 3 | 5 | 2019-07-21 | scroll_down |
| 3 | 5 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
Write an SQL query to find the average number of sessions per user for a period of 30 days ending 2019-07-27inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period.
SELECT ROUND(IFNNULL(SUM(a.num)/COUNT(a.user_id), 0), 2) AS average_sessions_per_user
FROM
(SELECT user_id, COUNT(DISTINCT session_id) AS num
FROM activity
WHERE activity_date BETWEEN ADDDATE('2019-07-27, INTERVAL -29 DAY) AND '2019-07-27'
GROUP BY user_id) AS a
;
SELECT ROUND(IFNULL(COUNT(DISTINCT session_id)/COUNT(DISTINCT user_id), 0), 2)
FROM activity
WHERE activity_date BETWEEN ADDDATE('2019-07-27', INTERVAL -29 DAY) AND '2019-07-27'
;
这道题有几个点需要注意：不要一看见per user就直接想用group by， 多想一下说不定有更方便的方法；有除法一定要加ifnull！！！；日期如果是inclusively的话，需要注意两个日期端点之间差了多少天。
10. Consecutive Available Seats
Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seats order by the seat_id using the following cinema table?
| seat_id | free |
|---------|------|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
Note:
The seat_id is an auto increment int, and free is bool ('1' means free, and '0' means occupied.).
Consecutive available seats are more than 2(inclusive) seats consecutively available.
SELECT DISTINCT a.seat_id
FROM cinema AS a, cinema AS b
WHERE ABS(a.seat_id-b.seat_id) = 1 AND a.free = 1 AND b.free = 1
ORDER BY seat_id
;
注意ABS使用的时候和DATEDIFF不一样，ABS中间是减号；还有就是这道题必须加distinct，注意join了以后再select的时候，有没有重复项，如果一时判断不清楚，就先加上。
11. Average Selling Price
Table: Prices
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| start_date | date |
| end_date | date |
| price | int |
+---------------+---------+
(product_id, start_date, end_date) is the primary key for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
Table: UnitsSold
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| purchase_date | date |
| units | int |
+---------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table indicates the date, units and product_id of each product sold.
Write an SQL query to find the average selling price for each product.
average_price should berounded to 2 decimal places.
The query result format is in the following example:
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date | price |
+------------+------------+------------+--------+
| 1 | 2019-02-17 | 2019-02-28 | 5 |
| 1 | 2019-03-01 | 2019-03-22 | 20 |
| 2 | 2019-02-01 | 2019-02-20 | 15 |
| 2 | 2019-02-21 | 2019-03-31 | 30 |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1 | 2019-02-25 | 100 |
| 1 | 2019-03-01 | 15 |
| 2 | 2019-02-10 | 200 |
| 2 | 2019-03-22 | 30 |
+------------+---------------+-------+
SELECT p.product_id,
ROUND(IFNULL(SUM(u.units*p.price)/SUM(u.units), 0), 2)
FROM unitssold AS u
INNER JOIN prices AS p
ON u.purchase_date BETWEEN p.start_date AND end_date AND u.product_id = p.product_id
GROUP BY p.product_id
;
这道题在Join的时候在匹配date的时候很容易忘记去匹配product_id，要注意一些。
