Percentage query w/ in 15 minute time ranges

[clemcrock]

Hello,

We have a site where our users, after signing up for our site and are flagged as 'verified', can upload Mp3's into our site. There are 3 tables that are involved: A users table which contains page_object_id (in this case will be the artist_id), a songs table which will record Mp3's uploaded and also contains an artist_id, and then the artist table which contains the information about the artist.

I'm trying to make a query that finds the percentage of users that, 1 hour after signing up for our site have uploaded an Mp3. The artist either uploads a mp3 or doesn't. The query will be fired off every 15 minutes during a 2 day date range.

This is the basic query that I'm trying:

SELECT 100.0 * (SELECT COUNT(*) FROM songs, users WHERE songs.artist_id = users.page_object_id) / ( SELECT COUNT(*) FROM users WHERE verified = 1 AND page_object_type = 'Artist' AND created_at < subdate(now(), INTERVAL 1 hour)) AS percentage
FROM users, songs WHERE users.page_object_type = 'Artist'
AND users.created_at < subdate(now(),INTERVAL 2 day)
AND songs.created_on > subdate(now(), INTERVAL 75 minute)
AND users.verified = 1 limit 1;

Something tells me that this isn't what I'm looking for.

Anyone know what will work better?

 

[clemcrock]

Ok - I'm now trying something simpler like this:

SELECT 100.0 * SUM(users.verified) / COUNT(songs.id) AS percentage
FROM users, songs
WHERE songs.artist_id = users.page_object_id
AND users.page_object_type = 'Artist'
GROUP BY users.verified, songs.id
limit 1;

Still don't know if that's any closer