# Data Explorer Queries for Solved Statistics

Here are a few Data Explorer queries that you can use for getting information about Solutions:

To list each individual “solved” event:

``````SELECT acting_user_id,
target_topic_id AS topic_id,
target_post_id,
created_at::date
FROM user_actions
WHERE action_type=15
ORDER BY created_at DESC
``````

To produce a list with stats per-user: (with variable for time interval, defaults to 1 year):

``````-- [params]
-- string :interval = 1 year

SELECT  ua.acting_user_id,
COUNT(CASE t.user_id WHEN ua.acting_user_id THEN 1 ELSE NULL END) as is_op,
COUNT(CASE t.user_id WHEN ua.acting_user_id THEN NULL ELSE 1 END) as not_op,
COUNT(*) as total
FROM user_actions ua
LEFT JOIN topics t ON target_topic_id = t.id
WHERE action_type=15
AND ua.created_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY ua.acting_user_id
ORDER BY total DESC
``````

To look up who has marked a particular topic as Solved:

``````-- [params]
-- topic_id :topic_id

SELECT target_topic_id,
acting_user_id,
created_at::date
FROM user_actions
WHERE target_topic_id = :topic_id
AND action_type = 15
ORDER BY created_at DESC
``````
3 Likes

DRAFT

This query would give solved/unsolved, the first reply | first replier | hours to first reply, as well as the solution post | solver | hours to solution (with category and ‘topic created since’ parameters).

If anyone wants to sanity check it before I add it to the OP that would be great.

``````
-- [params]
-- int :category_id = 6
-- date :since = 2013-01-01

WITH solved_topics AS (

SELECT ua.target_topic_id AS topic_id,
ua.user_id,
ua.target_post_id AS post_id,
p.created_at
FROM user_actions ua
JOIN posts p on p.id = ua.target_post_id
WHERE action_type = 15

),

SELECT p.topic_id,
MIN(id) AS post_id
FROM posts p
WHERE p.deleted_at ISNULL
AND p.post_type = 1
AND p.post_number > 1
GROUP BY p.topic_id
)

SELECT CASE WHEN st.topic_id IS NOT NULL THEN 'Solved' ELSE 'Unsolved' END AS "Solved?",
t.id as topic_id,
t.user_id AS question_user_id,
t.created_at::date AS "Topic Posted On:",
t.views,
COALESCE(CEIL(extract(epoch FROM (p.created_at - t.created_at))/3600.00),0) AS "Time to First Reply (hrs)",
st.user_id AS solved_by_user_id,
COALESCE(st.post_id,0) AS solution_post_id,
COALESCE(CEIL(extract(epoch FROM (st.created_at - t.created_at))/3600.00),0) AS "Time to Solution (hrs)"
FROM topics t
LEFT JOIN solved_topics st ON t.id = st.topic_id
LEFT JOIN first_reply fr ON fr.topic_id = t.id
LEFT JOIN posts p ON p.id = fr.post_id
WHERE t.category_id = :category_id
AND t.created_at >= :since
AND t.deleted_at ISNULL
AND t.visible = TRUE
ORDER BY t.created_at

``````