SQL/DataLemar (13) 썸네일형 리스트형 [PostgreSQL] (Medium) International Call Percentage [Verizon SQL Interview Question] 문제: https://datalemur.com/questions/international-call-percentage 나의 풀이: SELECT * FROM phone_calls c JOIN (SELECT caller_id , country_id AS caller_country FROM phone_info) i1 ON c.caller_id = i1.caller_id JOIN (SELECT caller_id AS receiver_id , country_id AS receiver_country FROM phone_info) i2 ON c.receiver_id = i2.receiver_id WHERE caller_country receiver_country; 발신자와 수신자의 국가를 조인하고 발신자와 수신자의 .. [PostgreSQL] (Medium) Card Launch Success [JPMorgan Chase SQL Interview Question] 문제: https://datalemur.com/questions/card-launch-success 나의 풀이: SELECT card_name , issued_amount FROM ( SELECT * , ROW_NUMBER() OVER (PARTITION BY card_name ORDER BY issue_year, issue_month) AS rownum FROM monthly_cards_issued) sub WHERE rownum = 1 ORDER BY issued_amount DESC; 풀긴 했지만 뭔가.. 뭔가 찝찝한 느낌쓰 더 간단하게 풀 수 있을 것 같은데..? 솔루션을 보니 MAKE_DATE() 이라는 내가 모르는 함수를 사용했다 SELECT MAKE_DATE(issue_year, issue_.. [PostgreSQL] (Medium) Compressed Mode [Alibaba SQL Interview Question] 문제: https://datalemur.com/questions/alibaba-compressed-mode 나의 풀이: DENSE_RANK() 사용 SELECT item_count AS mode FROM ( SELECT * , DENSE_RANK() OVER(ORDER BY order_occurrences DESC) AS ranking FROM items_per_order) sub WHERE ranking = 1 ORDER BY 1; 오늘것도 쉽다! 하고 풀었는데 솔루션 보니까 또 다른 풀이가 있다 첫번째는 MAX() 쓰는 방법 SELECT item_count AS mode FROM items_per_order WHERE order_occurrences = ( SELECT MAX(order_occurre.. [PostgreSQL] (Medium) Histogram of Users and Purchases [Walmart SQL Interview Question] 문제: https://datalemur.com/questions/histogram-users-purchases 나의 풀이: 웨어 조건절에 서브쿼리 쓰기 SELECT transaction_date , user_id , COUNT(*) AS purchase_count FROM user_transactions WHERE (transaction_date, user_id) IN ( SELECT MAX(transaction_date) AS transaction_date , user_id FROM user_transactions GROUP BY user_id) GROUP BY user_id, transaction_date ORDER BY 1; 솔루션: 랭크 윈도우 함수 사용 및 위드문 사용 WITH tb AS ( S.. [PostgreSQL] (Medium) Odd and Even Measurements [Google SQL Interview Question] 문제: https://datalemur.com/questions/odd-even-measurements 나의 풀이 는 없다! 마이에스큐엘이었으면 풀 수 있었을 것 같은데 포스트그레는 날짜 추출 부분 문법이 달라가지구.. 어떻게 하나 찾아봤더니! SELECT measurement_day , SUM(measurement_value) FILTER (WHERE rnum % 2 != 0) AS odd_sum , SUM(measurement_value) FILTER (WHERE rnum % 2 = 0) AS even_sum FROM ( SELECT CAST(measurement_time AS DATE) AS measurement_day , measurement_value , ROW_NUMBER() OVER ( PA.. [PostgreSQL] (Medium) Supercloud Customer [Microsoft SQL Interview Question] 문제: https://datalemur.com/questions/supercloud-customer 나의 풀이 SELECT customer_id FROM customer_contracts JOIN products USING (product_id) GROUP BY customer_id HAVING COUNT(DISTINCT product_category) = ( SELECT COUNT(DISTINCT product_category) FROM products) ORDER BY customer_id; 못 풀 수도 있겠는데? 싶었는데 풀어서 기분 조타 ^^ 위드문을 사용한 솔루션 쿼리 WITH supercloud AS ( SELECT customers.customer_id, COUNT(DISTINCT produ.. [PostgreSQL] (Medium) Signup Activation Rate [TikTok SQL Interview Question] 문제: https://datalemur.com/questions/signup-confirmation-rate 풀이 SELECT ROUND(COUNT( CASE WHEN email_id IN ( SELECT email_id FROM texts WHERE signup_action = 'Confirmed') THEN 1 END) ::decimal / COUNT(email_id) ::decimal, 2) FROM emails; 1. 마이에스큐엘과 딱 하나 다른 부분! 바로 데이터 타입 지정하기 (:: decimal) '타입 캐스팅'이라고 한다고 한다 풀이에는 카운트 마다 붙여줬는데 둘 중에 한 군데에만 붙여줘도 똑같이 작동한다 찾아보니 count returns type bigint in Postgresql 라고.. [PostgreSQL] (Medium) Top 5 Artists [Spotify SQL Interview Question] 문제: https://datalemur.com/questions/top-fans-rank 나의 풀이 WITH tb AS ( SELECT artist_name , song_id , COUNT(*) AS cnt FROM artists a JOIN songs s USING (artist_id) JOIN global_song_rank g USING (song_id) WHERE rank 이전 1 2 다음
