Hi Samantha,
If you wanted to refine those queries to include start and end date parameters, you could use a query like this:
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
SELECT
p.created_at,
cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.created_at BETWEEN :start_date AND :end_date
ORDER BY cr.id DESC
Example Results:
| created_at | post | model_used | negative | neutral | positive |
|---|---|---|---|---|---|
| 2023-11-08T21:21:23.913Z | post_id | sentiment | 58 | 38 | 2 |
The important part added here is joining the posts table with the classification_results table so that we can add:
AND p.created_at BETWEEN :start_date AND :end_date
To the WHERE statement to filter the posts by their created_at date.
Since the emotion and toxicity classification queries are similar, you could modify those queries with this addition to include start and end date parameters as well. 