Adding a date parameter

Community Building Data & reporting
3

Ah yes, it doesn’t like those inline comments next the parameters.

-- [params]
-- date :start_date 
-- date :end_date 

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

This one seems to work better. :+1: (You may need to refresh the page the first time to pick up the parameter input boxes)

Thanks for helping me de-bug the guides. :slight_smile: :trophy: