XF 2.1 Looking for help with a few SQL queries...

[Kevin]

Yes, SQL queries. I'm looking to just get some quick stats, nothing for an add-on or anything else like that, just a few quick numbers obtained from (hopefully) a few quick queries. Some of these are easy enough, others though I'm not too sure about the correct joins & groupby's that'd be needed.

For a specified list of thread IDs I'm looking to get the following info...

• ... how many unique user accounts posted in the threads?
• ... how many unique user accounts posted an attachment in the threads?
• ... how many unique user accounts posted an unique attachment in each of the threads?
• ... how many unique attachments were posted in the threads?
• ... how many posts (replies, not including the first post which created the thread) were made in the threads?

Thanks

 

[Kirby]

How many unique user accounts posted in the threads?

SELECT COUNT(DISTINCT user_id) FROM xf_post WHERE thread_id IN (x,z,y)

how many unique user accounts posted an attachment in the threads?

SELECT COUNT(DISTINCT user_id) FROM xf_post WHERE attach_count > 0 AND thread_id IN (x,y,z)

how many unique user accounts posted an unique attachment in each of the threads?
how many unique attachments were posted in the threads?
Please clarify "unique attachment":
Globally unique (e.g within the whole installation), unique within those threads or unique within each of those threaads?

how many posts (replies, not including the first post which created the thread) were made in the threads?

SELECT SUM(reply_count) FROM xf_thread WHERE thread_id IN (x,y,z)

Untested, no guarantess, YMMV.

 

[Kevin]

Thanks, Kirby! I'll try them out when I'm back at my home machine.

Please clarify "unique attachment":
Globally unique (e.g within the whole installation), unique within those threads or unique within each of those threaads?

Good question. The idea would be to find out.... (a) "How many users posted at least one attachment in each of the threads and the attachment is unique within the threads?" For a 'daily posting' type challenge this will answer how many users completed the challenge. The second part would be... (b) "How many users posted at least one attachment that is unique within the threads?" For the 'daily posting' type challenge that'll answer how many users took part in the challenge but did not complete it.

Thanks