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不管是用月份年份還是天數(shù)相減叫胖，最后得到的都是天數(shù)。

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的時候吩谦，有沒有重復(fù)項，如果一時判斷不清楚膝藕，就先加上式廷。

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