This is an SQL version of the Dashboard Report for Accepted Solutions.
This report provides a count of posts that have been marked as solutions on a daily basis within a specified date range. It is useful for understanding how often users are finding answers to their questions and marking them as solutions over time.
--[params]
--date :start_date
--date :end_date
SELECT
pcf.created_at::date as day,
COUNT(pcf.post_id) as accepted_solutions
FROM post_custom_fields pcf
WHERE pcf.name = 'is_accepted_answer'
AND pcf.created_at::date BETWEEN :start_date AND :end_date
GROUP BY pcf.created_at::date
SQL Query Explanation
The SQL query operates on the post_custom_fields
table, which stores additional metadata about posts. Here’s a breakdown of the query:
- Parameters: The query accepts two parameters,
:start_date
and:end_date
, which allow the user to specify the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
. - Selection: The query selects two fields:
pcf.created_at::date
asday
: This converts the timestamp of when the custom field was created into a date format and labels it as “day”.COUNT(pcf.post_id)
: This counts the number of post IDs that have been marked as solutions.
- Filtering: The
WHERE
clause filters the results to include only those records where thename
of the custom field is'is_accepted_answer'
, indicating that the post has been marked as a solution. It also ensures that the date when the post was marked as a solution falls between the start and end dates provided by the parameters. - Grouping: The
GROUP BY pcf.created_at::date
clause groups the count by each date, allowing us to see the number of solutions marked on each day within the specified range.
The output of this query will be a list of dates with the corresponding count of posts marked as solutions on each of those dates.
Example Results
day | accepted_solutions |
---|---|
2023-11-14 | 16 |
2023-11-15 | 5 |
2023-11-16 | 10 |
2023-11-17 | 4 |
2023-11-18 | 2 |
… | … |