Avg Subs Last 24h

TODO: collapasable, copyable, download, etc

Using postgres, please write me the sql to aggregate the average number of fans that subscribed to a show. There is a subscriber table with entries for each fanId, showId and date. I want to aggregate all shows by showId and then find for the last 24 hours, what was the average number of subscribers to shows. Please be breifSure! Here's the SQL query to aggregate the average number of fans who subscribed to each show in the last 24 hours:

SELECT showId, AVG(COUNT(*)) AS average_subscribers
FROM subscriber
WHERE date >= NOW() - INTERVAL '24 hours'
GROUP BY showId;

This query retrieves the showId from the subscriber table, filters the entries for the last 24 hours using the date column, groups the results by showId, and then calculates the average number of subscribers for each show using AVG(COUNT(*)).

Please note that this query assumes you have a table named subscriber with columns fanId, showId, and date. Adjust the table and column names according to your database schema.I want a single row returned, which is the average number of subscribers per show looking across all shows in a 24 hour periodApologies for the confusion. To calculate the overall average number of subscribers across all shows within the last 24 hours, you can use the following SQL query:

SELECT AVG(subscriber_count) AS average_subscribers
FROM (
  SELECT COUNT(*) AS subscriber_count
  FROM subscriber
  WHERE date >= NOW() - INTERVAL '24 hours'
  GROUP BY showId
) AS subquery;

This query first groups the subscriber table by showId, counts the number of subscribers for each show within the last 24 hours, and then calculates the average of those counts. The result is a single row with the average number of subscribers per show.

Avg Subs Last 24h created at Tue Jan 20 1970 12:31:33 GMT+0000 (Coordinated Universal Time)