Dashboard Report - Accepted Solutions

data & reporting
, , ,
1

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 of YYYY-MM-DD .
  • Selection: The query selects two fields:
    • pcf.created_at::date as day: 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 the name 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
… …
1 Like