03. [데분당태 챌린지 3주차] SQL_미션_풀이

선요약

• 문제풀이
  • LV1 - WORKFRONT [인사/ HR 서비스]
  • LV2 -UBER [모빌리티 서비스]
  • LV3 - AMAZON [이커머스]
  • LV4 - AIRBNB [주택 공유 서비스]
  • LV5 - GOOGLE [검색 서비스]
• 단계별 난이도
  • [LV1] 까지 푼 당신은 [SQL 탐험가]
  • [LV2] 까지 푼 당신은 [SQL 숙련자]
  • [LV3] 까지 푼 당신은 [SQL 전문가]
  • [LV4] 까지 푼 당신은 [SQL 고수]
  • [LV5] 까지 푼 당신은 [SQL 마스터]

 

내용

모든 문제가 ChatGPT를 통해 만들어진 문제로, 단계별 실력 테스팅이 가능합니다. (하지만 난이도는 그렇게 높지 않은 듯한 느낌입니다. 그리고 Chat GPT에게 해당 문제를 전달하면, 실제로 쿼리 답안까지 출력하는 놀라운 성능을 볼 수 있습니다.)

 

1. LV1 - WORKFRONT [인사/ HR 서비스]

```

아래의 열들로 이루어진 [직원]이라는 데이터베이스 테이블이 있습니다.
Given a database table named "employees" with the following columns:

- employee_id (integer)
- first_name (varchar)
- last_name (varchar)
- manager_id (integer)
- salary (decimal)

이 때 각 직원의 성, 이름과 그 직원의 매니저 성, 이름, 그리고 직원과 매니저의 연봉이 얼마나 차이가 나는지를 나타내는 테이블을 SQL 쿼리로 만들어주세요. 이 때 연봉 차이가 큰 순서대로 행을 나열해주세요.
Write a SQL query that returns the first and last names of each employee and the first and last names of their manager, as well as the salary difference between them, sorted by the salary difference in descending order.

```

 

2. LV2 -UBER [모빌리티 서비스]

```

우버 이용자들이 서비스를 사용한 데이터를 기록한 [여행] 이라는 테이블이 있습니다. 
테이블은 아래의 열들을 가지고 있습니다.
Consider a table named "trips" that contains information about all the trips taken by Uber customers. The table has the following columns:

- trip_id (integer)
- driver_id (integer)
- passenger_id (integer)
- start_time (datetime)
- end_time (datetime)
- distance (decimal)
- trip_fare (decimal)

각 운전자가 여행한 총 거리와 총 서비스 비용, 그리고 마일(Mile) 당 평균 가격을 보여주는 쿼리를 작성해주세요. 이 때 마일 당 평균 가격이 큰 순서대로 행을 나열해주세요.
Write a SQL query that returns the total distance and trip fare for all trips taken by each driver, along with the average trip fare per mile, sorted by the average trip fare per mile in descending order.

```

 

3. LV3 - AMAZON [이커머스]

```

Amazon 고객이 주문한 모든 주문에 대한 정보가 포함된 "주문"이라는 테이블이 있습니다. 
테이블에는 다음과 같은 열들이 있습니다.
Consider a table named "orders" that contains information about all the orders placed by Amazon customers. The table has the following columns:

- order_id (integer)
- customer_id (integer)
- order_date (datetime)
- product_id (integer)
- quantity (integer)
- price (decimal)

가장 자주 주문하는 상위 10개 제품을 판매된 총 갯수와 및 총 수익과 함께 총 판매 갯수가 큰 순서대로 정렬하는 SQL 쿼리를 작성해주세요.
Write a SQL query that returns the top 10 most frequently ordered products, along with the total number of units sold and total revenue generated by each product, sorted by the total number of units sold in descending order.

```

 

4. LV4 - AIRBNB [주택 공유 서비스]

```

Consider a database with two tables, named "listings" and "reservations", that contains information about all the properties listed on Airbnb and the reservations made for them. The tables have the following columns:
Airbnb에 등록된 모든 숙소와 해당 숙소에 대한 예약에 대한 정보가 포함된 "listings" 및 "reservations"라는 두 개의 테이블이 있는 데이터베이스가 있습니다. 두개의 테이블에는 테이블에는 다음 열이 있습니다.

- listings table:
    - listing_id (integer)
    - host_id (integer)
    - property_type (varchar)
    - room_type (varchar)
    - city (varchar)
- reservations table:
    - reservation_id (integer)
    - listing_id (integer)
    - checkin_date (date)
    - checkout_date (date)
    - number_of_guests (integer)

Write a SQL query that returns the total number of reservations and the average number of guests per reservation for each property type, grouped by city and room type, ordered by total number of reservations in descending order.
도시 및 객실 유형별로 그룹화되고 총 예약 수를 기준으로 내림차순으로 정렬된 각 속성 유형에 대한 총 예약 수 및 예약당 평균 손님 수를 반환하는 SQL 쿼리를 작성합니다.

```

 

5. LV5 - GOOGLE [검색 서비스]

```

Consider a table named "search_queries" that contains information about all the search queries made on Google. The table has the following columns:

- query_id (integer)
- query_text (varchar)
- user_id (integer)
- search_time (datetime)
- search_result (varchar)

Write a SQL query that returns the top 10 most frequently searched query terms, along with the total number of times each term was searched, and the average number of unique users that searched for each term, sorted by the total number of times searched in descending order.

```