Callum,
It's not too complicated when you break it down into pieces:
Starting from the top:
SELECT COUNT(DISTINCT ua.UserID)
FROM UserActions ua
gives us a resultset with which to filter our eventual query. The COUNT(DISTINCT ua.UserID) simply counts the number of unique UserID values we get after doing the two INNER JOINs:
The first:
INNER JOIN (
SELECT UserID FROM UserActions
WHERE Action = 'Request Download'
) AS uad
ON ua.UserID = aud.UserID
joins the main result set via an intersection (INNER JOIN) to a copy of the UserActions table filtered for only the 'Download Request' actions. We join on the UserID column.
The second:
INNER JOIN (
SELECT UserID FROM UserActions
WHERE Action = 'Page View'
) AS uap
ON ua.UserID = aup.UserID
does the exact same for the 'Page View' actions.
By doing an INNER JOIN, we know that the only results returned from the join will be those records which intersected. In the case of the first joined table, we're returning the UserIDs having requested a download. The records in the main UserActions resultset (ua) are filtered based on the UserID values returned in the join (uad). The records in the main UserActions resultset (ua) are further filtered for only UserID values returned by the second joined set (uap).
Hope this explains everything!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com