- ************************************************************************************************
- *****************************************
****************************
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
acceptance_rate --------------+------- 0.01
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'
Explanation
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