Strategy Analytics

trigger tracker

TABLE 
    Profile AS "Profile",
    Strategy AS "Strategy Attempted",
    Trigger/Context AS "Active Trigger",
    RegulationState AS "State",
    Effectiveness AS "Score"
FROM "03_observations/Daily"
WHERE Effectiveness <= 2 AND Trigger/Context != null
SORT date DESC

one week history

TABLE 
    Profile AS "Learning Profile", 
    Subject AS "Subject", 
    Strategy AS "Strategy Tested", 
    Effectiveness AS "Score (1-5)", 
    Independence AS "Independence Level"
FROM "📁 03_Observations"
WHERE type = "daily-log" AND date >= date(today) - dur(7 days)
SORT date DESC, Profile ASC

progress over time

TABLE 
    round(average(rows.Effectiveness), 1) AS "Avg Effectiveness (1-5)",
    count(rows.Effectiveness) AS "Total Days Logged",
    rows.Strategy AS "Strategies Tried"
FROM "📁 03_Observations"
WHERE type = "daily-log" AND Effectiveness != null
GROUP BY Profile
SORT round(average(rows.Effectiveness), 1) DESC

history by profile

TABLE strategy_used AS "Strategy", effectiveness AS "Effectiveness (1-5)", independence AS "Independence", subject_area AS "Subject"
FROM "📁 03_Observations"
WHERE type = "daily-log" AND profile_id = "Profile_X92J"
SORT date DESC

high-performing strategies

TABLE WITHOUT ID strategy_used AS "Strategy", choice(type = "daily-log", round(average(effectiveness), 1), 0) AS "Avg Effectiveness (1-5)"
FROM "📁 03_Observations"
WHERE type = "daily-log"
GROUP BY strategy_used
SORT average(effectiveness) DESC

by designation

TABLE 
    file.link AS "Profile",
    bc_designation AS "BC Designation",
    round(average(rows.Effectiveness), 1) AS "Avg Success",
    rows.Strategy AS "Strategies Tried"
FROM "📁 01_Profiles"
FLATTEN LINKED TO THIS (FROM "📁 03_Observations/Daily") AS rows
WHERE type = "learning-profile" AND bc_designation != null
GROUP BY file.link, bc_designation
SORT bc_designation ASC