20221128 sql

-- 코드를 입력하세요
-- 상품코드 별 매출액(판매가 * 판매량) 합계를 출력
-- 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬


SELECT A.PRODUCT_CODE
        , SUM(A.PRICE*B.SALES_AMOUNT) 매출액
  FROM PRODUCT A, OFFLINE_SALE B
  
 WHERE A.PRODUCT_ID = B.PRODUCT_ID
 
GROUP BY A.PRODUCT_CODE
ORDER BY 매출액 DESC, PRODUCT_CODE ASC





-- mysql  join

SELECT P.PRODUCT_CODE, (P.PRICE * SUM(O.SALES_AMOUNT)) AS SALES
FROM PRODUCT P
JOIN OFFLINE_SALE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC


-- mysql 조인, 서브쿼리

select PRODUCT_CODE, SUM(MUL) as SALES
from(
    select A.PRODUCT_ID, A.PRODUCT_CODE, (B.SALES_AMOUNT * A.PRICE) as MUL
    from PRODUCT as A
    join OFFLINE_SALE as B
    on A.PRODUCT_ID = B.PRODUCT_ID
    group by OFFLINE_SALE_ID
) as main
group by PRODUCT_CODE
order by SALES desc, PRODUCT_CODE asc



===============



-- ORACLE  1

-- 코드를 입력하세요
/* SALES_DATE, USER_ID, PRODUCT_ID */

SELECT SUBSTR(joined,1,4) as YEAR, to_number(SUBSTR(joined, 5,2)) as MONTH, puchased_users as PUCHASED_USERS,
       ROUND(puchased_users/(SELECT count(*) t_join_user FROM USER_INFO
         WHERE TO_CHAR(JOINED, 'YYYY') = '2021'
        GROUP BY TO_CHAR(JOINED, 'YYYY')),1) as PUCHASED_RATIO
 FROM (
 SELECT joined, count(PUCHASED_USERS) PUCHASED_USERS
  FROM (SELECT DISTINCT TO_CHAR(b.SALES_DATE, 'YYYYMM') as joined, b.USER_ID as PUCHASED_USERS
          FROM user_info a, online_sale b
         WHERE 1=1 
           AND a.user_id = b.user_id
           AND TO_CHAR(a.joined, 'YYYY') = '2021') --2021년에 가입
        GROUP BY joined)
ORDER BY YEAR, to_number(SUBSTR(joined, 5,2))


-- WITH 절 이용 

WITH USER_2021 AS (
    SELECT USER_ID
         , COUNT(*) OVER() AS TOTAL_CNT
      FROM USER_INFO
     WHERE TO_CHAR(JOINED, 'YYYY') = '2021'
)
, YYYYMM_CNT AS (
    SELECT DISTINCT TO_CHAR(B.SALES_DATE, 'YYYYMM') AS SALES_DATE_YYYYMM
         , A.USER_ID
      FROM USER_2021   A
         , ONLINE_SALE B
     WHERE A.USER_ID = B.USER_ID   
)
SELECT SUBSTR(SALES_DATE_YYYYMM, 1,4) AS YEAR
     , SUBSTR(SALES_DATE_YYYYMM, 5,6) AS MONTH
     , COUNT(*)
     , ROUND(COUNT(*) / (SELECT TOTAL_CNT FROM USER_2021 WHERE ROWNUM = 1), 1)
  FROM YYYYMM_CNT
 GROUP BY SUBSTR(SALES_DATE_YYYYMM, 1,4), SUBSTR(SALES_DATE_YYYYMM, 5,6)
 ORDER BY 1,2



-- 오라클 3 정답 예제에서 MONTH는 1, 2 형식이지만 포멧은 FMMM이 아니라 MM으로 해야 통과되네요

SELECT
    TO_CHAR(SALES_DATE, 'YYYY') AS YEAR,
    TO_CHAR(SALES_DATE, 'MM') AS MONTH,
    COUNT(DISTINCT UI.USER_ID) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT UI.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(JOINED, 'YYYY') = 2021), 1) AS PUCHASED_RATIO
FROM
    ONLINE_SALE OS
    JOIN USER_INFO UI
    ON OS.USER_ID = UI.USER_ID
    AND TO_CHAR(JOINED, 'YYYY') = '2021'
GROUP BY
    TO_CHAR(SALES_DATE, 'YYYY'),
    TO_CHAR(SALES_DATE, 'MM')
ORDER BY
    YEAR, MONTH;



# -- 요구 사항

# -- 1. 2021년에 가입한 회원을 대상으로 함
# -- 2. 상품의 구매 인원과, 2021년 가입자 대비 구매 인원의 비율 집계
# -- 3. 비율은 소수점 두번째 자리에서 반올림해야 함
# -- 4. 위의 데이터를 각 연도-월 별로 집계 
# -- 5. 연도-월 별로 오름차순 순차 정렬 할 것

SELECT
    -- 결제 정보에서 연도와 월을 컬럼으로 가져온다
    YEAR(ONLINE_SALE.SALES_DATE) AS YEAR,
    MONTH(ONLINE_SALE.SALES_DATE) AS MONTH,
    -- 결제 정보에 기록된 USER_ID의 고유값 집계 (결제 회원의 수)
    count(DISTINCT ONLINE_SALE.USER_ID) AS PURCHASED_USERS,
    -- 이용자 정보의 수(전체 이용자 수)를 결제 회원의 수로 나눈 것 (전체 이용자 대비 결제 회원의 비율)
    round (count(DISTINCT ONLINE_SALE.USER_ID) / 
           -- 서브 쿼리를 이용하여 그룹화 되지 않은 테이블의 2021년 총 가입자수를 가져온다
           (SELECT count(USER_ID) FROM USER_INFO WHERE YEAR(USER_INFO.JOINED) = 2021)
           -- 2번째 자리에서 반올림
           ,1) AS PURCHASED_RATIO

-- USER_ID 컬럼을 기준으로 이용자 정보와 결제 정보 테이블을 INNER JOIN
FROM ONLINE_SALE JOIN USER_INFO
ON ONLINE_SALE.USER_ID = USER_INFO.USER_ID

-- 가입일이 2021년인 회원만 JOIN
WHERE YEAR(USER_INFO.JOINED) = 2021

# -- 각 (결제) 월별로 집계 (연도를 따로 집계할 필요 없음)
GROUP BY MONTH

-- 연도와 월별로 순차 정렬
ORDER BY YEAR, MONTH ASC