29:null 2a:["$","title",null,{"children":"Real-World Data Apps with SQLite Cloud: Part 1 | jason a. grafft | Observable"}] 2f:T4cb,Inputs.table(evaluation_times_per_candidate_for_session, { columns: ["candidate", "Cooking", "Sewing", "CPR", "Cleaning", "Shopping", "Laundry"], header: { candidate: "Candidate" }, rows: 24, format: { candidate: d => `${d.slice(0,8)}...`, Cooking: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["Cooking"], val: d, color: color_palette["Cooking"], outcome: d >= 0 })}`, CPR: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["CPR"], val: d, color: color_palette["CPR"], outcome: d >= 0 })}`, Sewing: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["Sewing"], val: d, color: color_palette["Sewing"], outcome: d >= 0 })}`, Cleaning: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["Cleaning"], val: d, color: color_palette["Cleaning"], outcome: d >= 0 })}`, Shopping: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["Shopping"], val: d, color: color_palette["Shopping"], outcome: d >= 0 })}`, Laundry: d => html`${relativeMaxBar({ max: max_evaluation_attempt_time_for_session["Laundry"], val: d, color: color_palette["Laundry"], outcome: d >= 0 })}`, } })30:[{"id":"55f1a850cb1844843c81a7b6db0a763dfa390d412a58dd50ea1c497da2fe8fef9de48561f2a1b2eba8a68ca1e2fe0f0c5b0a664986f01ce392bf4db7b597c5af","href":"https://static.observableusercontent.com/files/55f1a850cb1844843c81a7b6db0a763dfa390d412a58dd50ea1c497da2fe8fef9de48561f2a1b2eba8a68ca1e2fe0f0c5b0a664986f01ce392bf4db7b597c5af","name":"evaluation_session_concept.db","type":"application/x-sqlite3","lastModified":1723062059773.27,"size":167936}] 2d:{"id":"c0dbfa4f897a5a31","slug":"real-world-data-apps-with-sqlite-cloud-part-1","title":"Real-World Data Apps with SQLite Cloud: Part 1","thumbnail":"bd171f65ffe3d22f0413af8a5b88f8872f21622a9dc7bf7078c888da711f6e80","access_level":"public","create_time":"$D2024-08-07T20:20:03.151Z","update_time":"$D2024-09-26T17:15:40.358Z","view_time":null,"first_public_time":"$D2024-09-11T03:45:15.398Z","has_unpublished_edits":null,"fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"33d714a841c4dfd5","login":"jagrafft","name":"jason a. grafft","avatar_url":"https://avatars-next.observableusercontent.com/50bfa5f99ef282cce89216516d1b5d3a73b8b6d3cc0bd65b3d18608b9b3f8957","tier":"starter_2024"},"version":1216,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"69c7ef7870d59485","login":"jagrafft","name":"jason a. grafft","avatar_url":"https://avatars-next.observableusercontent.com/50bfa5f99ef282cce89216516d1b5d3a73b8b6d3cc0bd65b3d18608b9b3f8957","tier":null}],"members":[{"type":"individual","id":"69c7ef7870d59485","login":"jagrafft","name":"jason a. grafft","avatar_url":"https://avatars-next.observableusercontent.com/50bfa5f99ef282cce89216516d1b5d3a73b8b6d3cc0bd65b3d18608b9b3f8957","tier":null,"role":"editor"},{"type":"team","id":"33d714a841c4dfd5","login":"jagrafft","name":"jason a. grafft","avatar_url":"https://avatars-next.observableusercontent.com/50bfa5f99ef282cce89216516d1b5d3a73b8b6d3cc0bd65b3d18608b9b3f8957","tier":"starter_2024","role":"viewer"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":3,"collection":null,"type":"notebook","body":{"title":"Real-World Data Apps with SQLite Cloud: Part 1","cells":[{"id":0,"value":"# Real-World Data Apps with SQLite Cloud: Part 1\n\nPaste a SQLiteCloud connection connection string in the text field , then check the box below to connect via the JavaScript SDK.\n\nLeave unchecked (and unfilled) to use the attached SQLite database.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":798,"value":"viewof sqlite_cloud_conn_str = Inputs.text({\n placeholder: \"SQLite Cloud Connection String...\",\n type: \"password\",\n width: \"600px\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":783,"value":"viewof use_sqlite_cloud = Inputs.toggle({\n label: \"Use SQLite Cloud\",\n disabled: !sqlite_cloud_toggle_available\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":781,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":380,"value":"#### Average Duration of Evaluations for Each Session","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":610,"value":"// TODO Bug? Max is always max of current session.\nInputs.table(average_evaluation_times_per_session, {\n columns: [\"session\", \"Cooking\", \"Sewing\", \"CPR\", \"Cleaning\", \"Shopping\", \"Laundry\"],\n header: {\n session: \"Session\"\n },\n format: {\n session: d => `${d.slice(0,8)}...`,\n Cooking: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"Cooking\"] })}`,\n CPR: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"CPR\"] })}`,\n Sewing: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"Sewing\"] })}`,\n Cleaning: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"Cleaning\"] })}`,\n Shopping: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"Shopping\"] })}`,\n Laundry: d => html`${relativeMaxBar({ max: d, val: d, color: color_palette[\"Laundry\"] })}`,\n }\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1155,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":813,"value":"viewof session_to_render = Inputs.select(sessions_in_database, {\n \"label\": \"Select Session\",\n \"width\": \"301px\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":377,"value":"#### Evaluation Duration for Each Candidate in Session `${session_to_render.slice(0,8)}...`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":442,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":674,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":48,"value":"## Connect to SQLiteCloud via the JavaScript SDK\n\nImport Observable helper to fetch `npm` packages","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":6,"value":"import { npm } from \"@pkg/npm\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":51,"value":"Import SQLiteCloud.io JavaScript SDK using `npm` helper if we are using SQLite Cloud","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":4,"value":"sqlitecloud = (sqlite_cloud_toggle_available && use_sqlite_cloud) ? npm('@sqlitecloud/drivers') : null","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":61,"value":"Connect to attached SQLite database or SQLiteCloud.io Project","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":768,"value":"project = (sqlite_cloud_toggle_available && use_sqlite_cloud) ? new sqlitecloud.Database(sqlite_cloud_conn_str) : FileAttachment(\"evaluation_session_concept.db\").sqlite()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1056,"value":"// Convenience function to switch between individual API's dot methods\nquery_project = (qry) => sqlitecloud ? project.sql(`USE DATABASE evaluation_session_concept; ${qry}`) : project.query(qry)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":65,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":676,"value":"## SQL","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":875,"value":"average_evaluation_times_per_session_sql = `WITH durations AS\n (SELECT at.session,\n at.evaluation,\n ((julianday(ot.end_ts) - julianday(at.start_ts)) * 24.0 * 60.0) AS duration_minutes\n FROM Attempt AS AT\n LEFT JOIN Outcome AS ot ON at.id = ot.attempt),\n avg_durations AS\n (SELECT session,\n evaluation,\n ROUND(AVG(duration_minutes), 2) AS avg_duration_minutes\n FROM durations\n GROUP BY session,\n evaluation)\nSELECT ad.session,\n ev.label AS evaluation,\n ad.avg_duration_minutes\nFROM avg_durations AS ad\nLEFT JOIN Evaluation AS ev ON ad.evaluation = ev.id;`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":600,"value":"average_evaluation_times_per_session = query_project(average_evaluation_times_per_session_sql)\n .then((res) => {\n return res.reduce((a,c) => {\n if (!a.hasOwnProperty(c[\"session\"])) {\n a[c[\"session\"]] = {\n \"session\": c[\"session\"]\n }\n }\n\n a[c[\"session\"]][c[\"evaluation\"]] = c[\"avg_duration_minutes\"]\n\n return a\n }, new Object({}))\n }).then((res) => {\n return Object.values(res)\n })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":942,"value":"evaluation_times_per_candidate_for_session_sql = `WITH durations AS\n (SELECT at.session,\n at.evaluation,\n at.candidate,\n ((julianday(ot.end_ts) - julianday(at.start_ts)) * 24.0 * 60.0) AS duration_minutes\n FROM Attempt AS AT\n LEFT JOIN Outcome AS ot ON at.id = ot.attempt\n WHERE SESSION = '${session_to_render}')\nSELECT ev.label AS evaluation,\n dur.candidate,\n ROUND(dur.duration_minutes, 2) AS duration_minutes\nFROM durations AS dur\nLEFT JOIN Evaluation AS ev ON dur.evaluation = ev.id;`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":953,"value":"evaluation_times_per_candidate_for_session = query_project(evaluation_times_per_candidate_for_session_sql)\n .then((res) => {\n return res.reduce((a,c) => {\n if (!a.hasOwnProperty(c[\"candidate\"])) {\n a[c[\"candidate\"]] = { \"candidate\": c[\"candidate\"]}\n }\n \n a[c[\"candidate\"]][c[\"evaluation\"]] = c[\"duration_minutes\"]\n \n return a\n }, new Object({}))\n })\n .then((res) => {\n return Object.values(res)\n })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1126,"value":"max_evaluation_attempt_time_for_session_sql = `WITH durations AS\n (SELECT at.session,\n at.evaluation,\n ROUND(((julianday(ot.end_ts) - julianday(at.start_ts)) * 24.0 * 60.0), 2) AS duration\n FROM Attempt AS AT\n LEFT JOIN Outcome AS ot ON at.id = ot.attempt\n WHERE SESSION = '${session_to_render}')\nSELECT ev.label AS evaluation,\n MAX(dur.duration) AS max_duration\nFROM durations AS dur\nLEFT JOIN Evaluation AS ev ON dur.evaluation = ev.id\nGROUP BY SESSION,\n evaluation;`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":983,"value":"max_evaluation_attempt_time_for_session = query_project(max_evaluation_attempt_time_for_session_sql).then((res) => {\n return res.reduce((a,c) => {\n a[c[\"evaluation\"]] = c[\"max_duration\"]\n \n return a\n }, new Object({}))\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":808,"value":"sessions_in_database = query_project(\"SELECT DISTINCT id FROM Session;\").then(res => res.map((r) => r.id))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":848,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":276,"value":"## Data, Functions, and Imports","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1013,"value":"color_palette = ((keys,colors) => {\n return keys.reduce((a,c,i) => {\n a[c] = colors[i]\n \n return a\n }, {})\n})(\n [\"Cleaning\", \"Cooking\", \"CPR\", \"Shopping\", \"Laundry\", \"Sewing\"],\n [\"#e1c4aa\", \"#a6dbe6\", \"#e6b8c4\", \"#c9e1ba\", \"#bebddf\", \"#9fc5b4\"]\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":449,"value":"function relativeMaxBar({\n val: _v,\n max: Max,\n color: Color,\n outcome: Outcome = true\n}) {\n var v = Math.abs(_v)\n return html`

${v.toFixed(2)}

`\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1089,"value":"sqlite_cloud_toggle_available = (() => {\n if (sqlite_cloud_conn_str !== \"\") {\n if (sqlite_cloud_conn_str.slice(0, 14) === \"sqlitecloud://\") {\n return true\n }\n }\n\n return false\n})()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W30","stdlib":"1","titling":"auto"}} 2e:"$2d:body"