# About This Book

### Introduction

This book provides questions about Database Hard Level on Leetcod and Hive SQL answers. (There are 28 questions in total)&#x20;

Hard level tests the ability of flexible ideas and require familiarity with the following :&#x20;

* Various Hive SQL functions, including Mathematical functions, data functions, string functions...&#x20;
* Multi-table join, including subquery, JOIN, UNION&#x20;
* Windows function

I strongly recommend that each question must be considered in-depth, and running Query to view the output results. **Do not** look at the answers directly, otherwise, it will reduce the learning effect.

In addition, the idea of solving the problem is not unique, so the answer is for reference only.



這本書提供Leetcod上 Database Hard Level的題目及Hive SQL解答，一共28題

Hard Level很考驗綜合運用的能力，你要具備靈活的思路以及熟悉以下查詢用法：

* 各種函數的運用
* 多表連結，包含子查詢、JOIN、UNION
* Windows function

即是能高度掌握<入門數據分析 掌握Hive SQL取數能力>課程所有內容

> 本課程已上架Hahow平台，如您對課程有興趣，可點擊[此鏈結](https://hahow.in/cr/andyrockhive)查看課程詳細說明



強烈建議每道題目需經過思考，若一時無法想出一步到位的Query，可先運行片段的Query查看輸出結果，與題目希望的result對比，再思考差異及延伸Query，勿直接看答案，否則學習效果將打(骨)折。

另外，解題思路並不唯一，故答案僅供參考。



### About leetcode SQL question

* [Leetcode](https://leetcode.com/) is one of the most well-known online judge platforms that you can use to practice your programming skills by solving coding questions
* Enter the Problem page and filter the topic of Database, here are all SQL questions.
* Problems are three difficulty levels (Easy, Medium, and Hard) , most of the questions need to be paid to unlock.

![](https://tva1.sinaimg.cn/large/008i3skNgy1gx6fffc6koj31dc0u0wjo.jpg)

### Hard Problems

This is a list of [LeetCode's Hard Level Database problems](https://leetcode.com/problemset/database/?difficulty=HARD\&page=1) and solutions with Hive SQL.

My online course in Hahow platform provides  the big data environment and _HUE_ (Hue is a Web interface for analyzing data with _Apache Hadoop_), you can create a  database (such as leetcode) to store the data for each exercises.

```sql
create database if not exists leetcode ;
```

<table><thead><tr><th width="150" align="center">exercise id</th><th width="438.72727272727275">exercise_name</th></tr></thead><tbody><tr><td align="center">1</td><td><a href="../exercise-1-185.department-top-three-salaries">185. Department Top Three Salaries</a></td></tr><tr><td align="center">2</td><td><a href="../exercise-2-262.-trips-and-users">262. Trips and Users</a></td></tr><tr><td align="center">3</td><td><a href="../exercise-3-569.median-employee-salary">569.Median Employee Salary</a></td></tr><tr><td align="center">4</td><td><a href="../exercise-4-571.find-median-given-frequency-of-numbers">571.Find Median Given Frequency of Numbers</a></td></tr><tr><td align="center">5</td><td><a href="../exercise-5-579.find-cumulative-salary-of-an-employee-problem">579.Find Cumulative Salary Of An Employee Problem</a></td></tr><tr><td align="center">6</td><td><a href="../exercise-6-601.human-traffic-of-stadium">601.Human Traffic of Stadium</a></td></tr><tr><td align="center">7</td><td><a href="../exercise-7-615.average-salary-departments-vs-company">615.Average Salary: Departments VS Company</a></td></tr><tr><td align="center">8</td><td><a href="../exercise-8-618.students-report-by-geography">618. Students Report By Geography</a></td></tr><tr><td align="center">9</td><td><a href="../exercise-9-1097.game-play-analysis-v">1097. Game Play Analysis V</a></td></tr><tr><td align="center">10</td><td><a href="../exercise-10-1127.user-purchase-platform">1127. User Purchase Platform</a></td></tr><tr><td align="center">11</td><td><a href="../exercise-11-1159.market-analysis-ii">1159. Market Analysis II</a></td></tr><tr><td align="center">12</td><td><a href="../exercise-12-1194.tournament-winners">1194. Tournament Winners</a></td></tr><tr><td align="center">13</td><td><a href="../exercise-13-1225.report-contiguous-dates">1225.Report Contiguous Dates</a></td></tr><tr><td align="center">14</td><td><a href="../exercise-14-1336.the-number-of-transactions-per-visit">1336.The number of transactions per visit</a></td></tr><tr><td align="center">15</td><td><a href="../exercise-15-1369.get-the-second-most-recent-activity">1369.Get The Second Most Recent Activity Proble</a></td></tr><tr><td align="center">16</td><td><a href="../exercise-16-1384.total-sales-amount-by-year">1384. Total Sales Amount by Year</a></td></tr><tr><td align="center">17</td><td><a href="../exercise-17-1412.find-the-quiet-students-in-all-exams">1412.Find the Quiet Students in All Exams</a></td></tr><tr><td align="center">18</td><td><a href="../exercise-18-1479.-sales-by-day-of-the-week">1479. Sales by Day of the Week</a></td></tr><tr><td align="center">19</td><td><a href="../exercise-19-1635.-hopper-company-queries-i">1635. Hopper Company Queries I</a></td></tr><tr><td align="center">20</td><td><a href="../exercise-20-1645.-hopper-company-queries-ii">1645. Hopper Company Queries II</a></td></tr><tr><td align="center">21</td><td><a href="../exercise21-1651.-hopper-company-queries-iii">1651. Hopper Company Queries III</a></td></tr><tr><td align="center">22</td><td><a href="../exercise-22-1767.find-the-subtasks-that-did-not-execute">1767.Find the Subtasks That Did Not Execute</a></td></tr><tr><td align="center">23</td><td><a href="../exercise-23-1892.page-recommendations-ii">1892.Page Recommendations II</a></td></tr><tr><td align="center">24</td><td><a href="../exercise-24-1917.leetcodify-friends-recommendations">1917.Leetcodify Friends Recommendations</a></td></tr><tr><td align="center">25</td><td><a href="../exercise-25-1919.leetcodify-similar-friends">1919.Leetcodify Similar Friends</a></td></tr><tr><td align="center">26</td><td><a href="../exercise-26-1972.first-and-last-call-on-the-same-day">1972.First and Last Call On the Same Day</a></td></tr><tr><td align="center">27</td><td><a href="../exercise-27-2004.the-number-of-seniors-and-juniors-to-join-the-company">2004.The Number of Seniors and Juniors to Join the Company</a></td></tr><tr><td align="center">28</td><td><a href="../exercise-28-2010.the-number-of-seniors-and-juniors-to-join-the-company-ii">2010.The Number of Seniors and Juniors to Join the Company II</a></td></tr></tbody></table>

