92. Acceptance rate first week of 2021

easy spotify

  • ************************************************************************************************
  • *********************************************************************

Table: request

Friend request and acceptance table by date.

  col_name     | col_type
 request_id     | bigint
 acceptance_id  | bigint
 request_dt     | date
 acceptance_dt  | date

Sample results


Solution postgres

SELECT SUM(CASE WHEN acceptance_dt IS NOT NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS acceptance_rate
FROM request
WHERE request_dt >=  '2021-01-01'
AND  request_dt <=  '2021-01-07'


This query is trying to calculate the acceptance rate of requests for a specific time period (from January 1st, 2021 to January 7th, 2021) in a table called "request" in a PostgreSQL database. The acceptance rate is calculated by taking the total number of accepted requests (where the "acceptance_dt" field is not null) and dividing it by the total number of requests made during that time period.

The query achieves this by using a conditional statement (CASE WHEN) to check whether each request was accepted or not. If the "acceptance_dt" field is not null, it is counted as an accepted request (1), otherwise, it is counted as a non-accepted request (0). The SUM function is used to add up all the 1's and 0's, giving us the total number of accepted requests.

The COUNT function is used to count the total number of requests in the given time period. The acceptance rate is then calculated by dividing the total number of accepted requests by the total number of requests and multiplying the result by 1.0 to ensure that the output is a decimal value.

Overall, this query is a useful tool for analyzing the efficiency of request acceptance during a specific time period

Expected results

More Spotify questions

ID Title Level FTPR
86 Top song in the US spotify easy
87 Top song in the US and UK spotify medium
88 Top song report spotify medium
89 Top 5 artists in the US spotify medium
90 Top artist report spotify medium
91 same day friend request acceptance rate spotify easy