Calculate the friend acceptance rate for each date when friend requests were sent. A request is sent if action = 'sent' and accepted if action = 'accepted'. If a request is not accepted, there is no record of it being accepted in the table. The output should only include dates where requests were sent and at least one of them was accepted (acceptance can occur on any date after the request is sent).

To compute the acceptance rate per date, you need two aggregates: the number of requests sent on each date and the number of those requests that were eventually accepted. A common approach is to use a CTE or subquery to count sent requests per date, then join to a second subquery that counts accepted requests per original sender and receiver pair. Finally, calculate the ratio.

Example using PostgreSQL:

WITH sent AS (
  SELECT user_id_sender, user_id_receiver, date AS sent_date
  FROM friend_actions
  WHERE action = 'sent'
),
accepted AS (
  SELECT user_id_sender, user_id_receiver, MIN(date) AS accepted_date
  FROM friend_actions
  WHERE action = 'accepted'
  GROUP BY user_id_sender, user_id_receiver
)
SELECT
  s.sent_date,
  COUNT(*) AS total_sent,
  COUNT(a.user_id_sender) AS total_accepted,
  ROUND(COUNT(a.user_id_sender)::numeric / COUNT(*) * 100, 2) AS acceptance_rate_pct
FROM sent s
LEFT JOIN accepted a
  ON s.user_id_sender = a.user_id_sender
 AND s.user_id_receiver = a.user_id_receiver
 AND a.accepted_date >= s.sent_date
GROUP BY s.sent_date
HAVING COUNT(*) > 0
   AND COUNT(a.user_id_sender) > 0
ORDER BY s.sent_date;

Explanation:

1. sent CTE captures all sent actions with their date.
2. accepted CTE finds the earliest acceptance date for each pair.
3. The main query left‑joins accepted to sent, ensuring the acceptance happened on or after the send date.
4. COUNT(*) gives total sent per date; COUNT(a.user_id_sender) counts only those that have a matching acceptance.
5. The rate is computed as accepted / sent.

Complexity: O(n) time for scanning the table twice and O(n) space for the CTEs, where n is the number of rows. This solution works for large datasets and can be optimized with proper indexes on (user_id_sender, user_id_receiver, action, date).