2020-08-21 easy 7-11

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:

+---------+------------+---------------+---------------+

+---------+------------+---------------+---------------+

| 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:

+------------+------------+------------+--------+

+------------+------------+------------+--------+

| 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，要注意一些。