[597_EASY] Friend Requests I : Overall Acceptance Rate

2022. 10. 4. 20:29·SQL/LeetCode

Problem Description –

Write an SQL query to find the overall acceptance rate of requests, which is the number of acceptance divided by the number of requests. Return the answer rounded to 2 decimals places.

Note That –

  1. The accepted requests are not necessarily from the table friend_request. In this case, Count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.
  2. It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.
  3. If there are no requests at all, you should return 0.00 as the accept_rate.

The query result format is in the following example.

Follow up:

  • Could you write a query to return the acceptance rate for every month?
  • Could you write a query to return the cumulative acceptance rate for every day?

 

문제 풀이

sender가 동일한 수신자에게 여러번 요청을 보낼 수 있고, 요청이 두 번 이상 수락될 수 있기 때문에 DISTINCT하게 sender_id, send_to_id를 COUNT해야함 -- requester/accepter도 동일

 

소수점 이하 두자리 까지 반올림 -> round,2 

 

request가 전혀 없으면 accept_rate를 0으로 반환 -> ifnull 

 

 

SELECT(
	IFNULL(
    	ROUND(
        	(SELECT COUNT(DISTINCT requester_id, accepter_id) FROM RequestAccepted)/
                (SELECT COUNT(DISTINCT sender_id, send_to_id) FROM FriendRequest)
              ,2)
        ,0)
 ) AS accept_rate

 

저작자표시 (새창열림)

'SQL > LeetCode' 카테고리의 다른 글

[1141_EASY] User Activity for the Past 30 Days I  (0) 2022.12.08
[603#_EASY]Consecutive Available Seats  (0) 2022.10.29
[596_EASY] Classes More Than 5 Students  (0) 2022.10.04
[595_EASY] Big Countries  (0) 2022.10.03
[586_EASY] Customer Placing the Largest Number of Orders  (0) 2022.10.03
'SQL/LeetCode' 카테고리의 다른 글
  • [1141_EASY] User Activity for the Past 30 Days I
  • [603#_EASY]Consecutive Available Seats
  • [596_EASY] Classes More Than 5 Students
  • [595_EASY] Big Countries
소금깨
소금깨
  • 소금깨
    고군분투 인생살이
    소금깨
  • 전체
    오늘
    어제
    • 분류 전체보기 (328)
      • SQL (271)
        • 프로그래머스 (27)
        • LeetCode (198)
        • Hacker Rank (27)
        • Solve SQL (1)
        • 개념 (15)
      • 데이터 분석 (16)
        • 참고하며 공부하기 (14)
      • 기타 (15)
        • 통계 (14)
      • 오류 (6)
      • 인생살이 (0)
        • 리뷰 (0)
        • 일기 (0)
      • 中文 (0)
      • TABLEAU (3)
  • 블로그 메뉴

    • 홈
    • 태그
    • 방명록
  • 링크

  • 공지사항

  • 인기 글

  • 태그

    파이썬을활용한시계열데이터분석AtoZ올인원패키지Online
    파이썬을활용한시계열데이터분석
    시계열데이터분석
    해커랭크
    SQL
    MySQL
    Hard
    easy
    HACKER_RANK
    패스트캠퍼스후기
    medium
    프로그래머스
    solvesql
    LeetCode
    직장인자기계발
    패캠챌린지
    group by
    프리미엄
    직장인인강
    패스트캠퍼스
  • 최근 댓글

  • 최근 글

  • hELLO· Designed By정상우.v4.10.3
소금깨
[597_EASY] Friend Requests I : Overall Acceptance Rate
상단으로

티스토리툴바