[SQL 코드카타] 10문제

< 프로그래머스 >

(76번). 상품을 구매한 회원 비율 구하기 

SELECT TO_CHAR(SALES_DATE, 'YYYY') YEAR,
       TO_NUMBER(TO_CHAR(SALES_DATE, 'MM')) MONTH,
       COUNT(DISTINCT A.USER_ID) PURCHASED_USERS,
       ROUND(COUNT(DISTINCT A.USER_ID)/(SELECT COUNT(*)
               				FROM USER_INFO
                                        WHERE 1=1
                                              AND TO_CHAR(JOINED, 'YYYY') = 2021),1) PUR_RATIO
FROM ONLINE_SALE A
WHERE USER_ID IN (SELECT USER_ID
                  FROM USER_INFO  
                  WHERE 1=1
                        AND TO_CHAR(JOINED, 'YYYY') = 2021)
GROUP BY TO_CHAR(SALES_DATE, 'YYYY'), TO_NUMBER(TO_CHAR(SALES_DATE, 'MM'))
ORDER BY YEAR, MONTH

 

< LEETCODE >

(77번). Recyclable and low fat products  

 

SELECT Product_id
FROM Products
WHERE low_fats = 'Y'
      AND recyclable = 'y'

 

(78번) Find Customer Referee 

SELECT name
FROM customer
WHERE referee_id != 2
      OR referee_id is null

(79번) Big Countries

SELECT name, population, area
FROM world
WHERE 1=1
      AND area >= 3000000
      OR population >= 25000000

 

(80번) Article views

SELECT DISTINCT AUTHOR_ID AS ID
FROM VIEWS
WHERE AUTHOR_ID = VIEWER_ID
ORDER BY AUTHOR_ID ASC

(81번) invalid-tweets

SELECT TWEET_ID
FROM TWEETS
WHERE LENGTH(CONTENT) > 15

(82번) replace-employee-id-with-the-unique-identifier

SELECT UNIQUE_ID, A.NAME
FROM EMPLOYEES A LEFT JOIN EMPLOYEEUNI B ON A.ID = B.ID

(83번) product-sales-analysis

SELECT B.PRODUCT_NAME, A.YEAR, A.PRICE
FROM SALES A, PRODUCT B
WHERE A.PRODUCT_ID = B.PRODUCT_ID

 

<Hacker Rank>

(127번) revising-the-select-query

SELECT *
FROM CITY
WHERE 1=1
      AND POPULATION > 100000 
      AND COUNTRYCODE LIKE 'USA%';

(128번) revising-the-select-query(2)

SELECT NAME
FROM CITY
WHERE POPULATION > 120000
      AND COUNTRYCODE LIKE 'USA%';

(129번) select all

SELECT *
FROM CITY