Often you will want to pull out data for a specific time period. You can do this by hard-coding in your time period into the query itself, but to make your report more future-proof you can add a date parameter:
This query is designed to count the total number of posts in each category (excluding deleted posts and topics, private messages, and topics without a category) that were created within a specific date range, and then order the categories by the total number of posts in descending order.
-- [params]
-- date :start_date -- This is a parameter for the start date of the range
-- date :end_date -- This is a parameter for the end date of the range
SELECT
t.category_id,
COUNT(p.id) AS "Total Posts" -- Count the number of posts in each category
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Filter posts that were created between the start date and the end date (::date casts the created_at timestamp as a date)
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype <> 'private message'
AND p.user_id > 0
AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
Using the parameter style date rather than string makes entering the parameter value much more intuitive, especially when shared with others.
Another notable tip for this type of query is to cast the created_at as a date. As the value is stored in the database as a timestamp, if you don’t cast it to a date the query won’t pick up the results from the date of the :end_date itself.
You can check which values are stored as timestamps using the Explorer tree:
This query is mainly an example to demonstrate the principles. Hopefully you find it useful, but if you have any questions please ask them below. 
(You may need to refresh the page the first time to pick up the parameter input boxes)