SQL문제풀기

[SQL 문제 풀이] Market Analysis I (시장 분석 I)

Jun 24, 2025

[SQL 문제 풀이] Market Analysis I (시장 분석 I)

Contents

내가 작성한 쿼리

Market Analysis I - LeetCode

Can you solve this real interview question? Market Analysis I - Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id is the primary key (column with unique values) of this table. This table has the info of the users of an online shopping website where users can sell and buy items. Table: Orders +---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ order_id is the primary key (column with unique values) of this table. item_id is a foreign key (reference column) to the Items table. buyer_id and seller_id are foreign keys to the Users table. Table: Items +---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ item_id is the primary key (column with unique values) of this table. Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019. Return the result table in any order. The result format is in the following example. Example 1: Input: Users table: +---------+------------+----------------+ | user_id | join_date | favorite_brand | +---------+------------+----------------+ | 1 | 2018-01-01 | Lenovo | | 2 | 2018-02-09 | Samsung | | 3 | 2018-01-19 | LG | | 4 | 2018-05-21 | HP | +---------+------------+----------------+ Orders table: +----------+------------+---------+----------+-----------+ | order_id | order_date | item_id | buyer_id | seller_id | +----------+------------+---------+----------+-----------+ | 1 | 2019-08-01 | 4 | 1 | 2 | | 2 | 2018-08-02 | 2 | 1 | 3 | | 3 | 2019-08-03 | 3 | 2 | 3 | | 4 | 2018-08-04 | 1 | 4 | 2 | | 5 | 2018-08-04 | 1 | 3 | 4 | | 6 | 2019-08-05 | 2 | 2 | 4 | +----------+------------+---------+----------+-----------+ Items table: +---------+------------+ | item_id | item_brand | +---------+------------+ | 1 | Samsung | | 2 | Lenovo | | 3 | LG | | 4 | HP | +---------+------------+ Output: +-----------+------------+----------------+ | buyer_id | join_date | orders_in_2019 | +-----------+------------+----------------+ | 1 | 2018-01-01 | 1 | | 2 | 2018-02-09 | 2 | | 3 | 2018-01-19 | 0 | | 4 | 2018-05-21 | 0 | +-----------+------------+----------------+

Market Analysis I - LeetCode

https://leetcode.com/problems/market-analysis-i/description/

Market Analysis I - LeetCode

notion image

notion image


2019년 구매자로서 각 사용자, 가입 날짜 및 주문 수를 찾기 위한 솔루션을 작성하세요.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL


SELECT
    u.user_id AS buyer_id,
    u.join_date,
    IFNULL(COUNT(o.order_id), 0) AS orders_in_2019
FROM
    Users u
LEFT JOIN
    Orders o
    ON u.user_id = o.buyer_id
        AND YEAR(o.order_date) = 2019 -- 19년도 인것만
GROUP BY
    u.user_id, u.join_date;

Oracle


SELECT
    u.user_id AS buyer_id,
    TO_CHAR(u.join_date, 'YYYY-MM-DD') AS join_date,
    NVL(COUNT(o.order_id), 0) AS orders_in_2019
FROM
    Users u
LEFT JOIN
    Orders o
    ON u.user_id = o.buyer_id
        AND EXTRACT(YEAR FROM o.order_date) = 2019
GROUP BY
    u.user_id, u.join_date;

차이점

항목	MySQL	Oracle
연도 추출 함수	`YEAR(date_col)`	`EXTRACT(YEAR FROM date_col)`
NULL 대체 함수	`IFNULL(expr, 0)`	`NVL(expr, 0)`
날짜 표시 방식 차이	`'2019-08-16'`	`TO_DATE('2019-08-16', 'YYYY-MM-DD')`

Share article

stupefyee

RSS·Powered by Inblog