Analyze User Engagement and Spammer Read-Rate in SQL

messages

+------------+------------+-------------+--------------+-----------+ | sender_id | receiver_id| message_id | sent_date | read_date | +------------+------------+-------------+--------------+-----------+ | 1232412 | 12342 | 124 | 2024-01-01 | 2024-01-01| | 6534354 | 423452 | 342 | 2024-01-02 | NULL | | 1234412 | 34214 | 425 | 2024-01-01 | 2024-01-05| +------------+------------+-------------+--------------+-----------+

​

violating_scores

+---------+----------------+-------------+ | user_id | violation_type | p_violating | +---------+----------------+-------------+ | 1232412 | spam | 0.4 | | 6534354 | scam | 0.9 | | 1234412 | impersonation | 0.1 | +---------+----------------+-------------+

Scenario

Messaging platform needs SQL analyses of user engagement and spammer behavior in the past week.

Question

How many users received more than 10 messages in a single day during the last 7 days? What is the average read-rate for spammers over the last week? Read-rate = (read messages sent by spammers) / (all messages sent by spammers).

Hints

Filter by last 7 days; join spammers from violating_scores; aggregate counts and ratios.