76번
문제 설명
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과
온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.
USER_INFO 테이블은 아래와 같은 구조로 되어있으며
USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
Column name Type Nullable
USER_ID INTEGER FALSE
GENDER TINYINT(1) TRUE
AGE INTEGER TRUE
JOINED DATE FALSE
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.
ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는
각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
Column name Type Nullable
ONLINE_SALE_ID INTEGER FALSE
USER_ID INTEGER FALSE
PRODUCT_ID INTEGER FALSE
SALES_AMOUNT INTEGER FALSE
SALES_DATE DATE FALSE
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서
2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와
상품을 구매한 회원의 비율(=2021년에 가입한 회원 중
상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을
년, 월 별로 출력하는 SQL문을 작성해주세요.
상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고,
전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
select date_format(sales_date, '%Y') year, date_format(sales_date, '%m') month
, count(distinct user_id) purchased_users
, round(count(distinct user_id)/(select count(user_id) from user_info where joined like '2021%'), 1) purchased_ratio
from user_info u
join online_sale o
using (user_id)
where joined like '2021-%'
group by 1, 2
order by 1, 2
오전 코드카타 시간에 어떻게 풀지 머리를 굴리는 도중 튜터님이 들어오셨다.
다들 어떻게 그렇게 아무렇지 않게 피드백을 받으시는지 대단할 따름이었다.
나는 따로 나가서 받았다 ㅎㅎ
이전 문제까지 다 cte구문을 활용하는 문제였어서 이 문제도 그래야 하나 고민하고 있었는데
깔끔하게 이 문제는 굳이 그렇게 사용할 필요 없다고 말씀해주셨다.
뤼튼을 활용하다보니 쉬운 개념도 헷갈릴 때가 있는데, 그런 지점을 확실히 짚어주셔서 감사했다.
group by 에서 뭘 지정해야하는지도 아직 숙지가 안돼있다. 사실 다른 문제들 풀 때도 많이 적어둔 거지만 group by 작동 방식을 아직 완전히 습득하지 못한듯 하다..!
이 문제는 대부분 상품 판매 데이터와 회원 데이터가 겹치는 부분만 보면 되는데,
한 가지 2021년도에 가입한 회원수를 구할 때만 user_info 부분이 필요하다.
이는 with 구문을 활용해도 좋을 것이고 위에 풀이처럼 서브쿼리를 활용해도 된다.
77번
이 문제부터는 드디어 마음 편한 영어 문제다.
그냥 정답만 적어뒀다!
Table: Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| low_fats | enum |
| recyclable | enum |
+-------------+---------+
product_id is the primary key (column with unique values) for this table.
low_fats is an ENUM (category) of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is not.
recyclable is an ENUM (category) of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means it is not.
Write a solution to find the ids of products that are both low fat and recyclable.
Return the result table in any order.
select product_id
from products
where low_fats = 'Y' and recyclable = 'Y'
행복했다.
78번
Table: Customer
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| referee_id | int |
+-------------+---------+
In SQL, id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.
Find the names of the customer that are not referred by the customer with id = 2.
Return the result table in any order.
select name
from customer
where referee_id is null or not referee_id = 2
79번
Table: World
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | bigint |
+-------------+---------+
name is the primary key (column with unique values) for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.
A country is big if:
it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).
Write a solution to find the name, population, and area of the big countries.
Return the result table in any order.
select name, population, area
from world
where population > 25000000 and area > 300000
80번
Table: Views
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
There is no primary key (column with unique values) for this table, the table may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
Note that equal author_id and viewer_id indicate the same person.
Write a solution to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.
select distinct author_id id
from views
where author_id = viewer_id
order by id