analysis.sql

-- get confidence rating by day
SELECT `confidence_rating`
FROM `reflections`
WHERE `creator_id` = ?
ORDER BY `created_at`;

-- average confidence rating by subject (bar)
SELECT `subject`, AVERAGE(`confidence_rating`)
FROM `reflections`
WHERE `creator_id` = ?
GROUP BY `subject`
ORDER BY `created_at`;

-- assessment types
SELECT `assessment_type`, COUNT(`assessment_type`)
FROM `reflections`
WHERE `creator_id` = ?
GROUP BY `subject`
ORDER BY `created_at`;

-- get prep days vs confidence rating
SELECT `prep_days`, `confidence_rating`
FROM `reflections`
WHERE `creator_id` = ?
ORDER BY `created_at`;

-- tags used most frequently (bar chart)

-- average confidence by assessment type (bar chart)
SELECT `assessment_type`, AVERAGE(`assessment_type`)
FROM `reflections`
WHERE `creator_id` = ?
GROUP BY `assessment_type`
ORDER BY `created_at`;

-- reflections per subject (bar chart)
SELECT `subject`, COUNT(`subject`)
FROM `reflections`
WHERE `creator_id` = ?
GROUP BY `subject`
ORDER BY `created_at`;