27:null 28:["$","title",null,{"children":"Real-World Data Apps with SQLite Cloud: Part 2 | jason a. grafft | Observable"}] 2d: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 })}`, } })2e:[] 2b:{"id":"3c5d2c8b557b3d4d","slug":null,"title":"Real-World Data Apps with SQLite Cloud: Part 2","thumbnail":"6e27eada29f27e1e9770ec54d07d4a29d8487b58e7c5e230919819285ff29918","access_level":"public","create_time":"$D2025-02-09T18:30:22.036Z","update_time":"$D2025-02-09T18:44:19.756Z","view_time":null,"first_public_time":"$D2025-02-09T18:44:58.941Z","has_unpublished_edits":null,"fork":{"id":"c0dbfa4f897a5a31","slug":"real-world-data-apps-with-sqlite-cloud-part-1","title":"Real-World Data Apps with SQLite Cloud: Part 1","owner":{"id":"33d714a841c4dfd5","login":"jagrafft","name":"jason a. grafft"}},"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":1241,"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":0,"collection":null,"type":"notebook","body":{"title":"Real-World Data Apps with SQLite Cloud: Part 2","cells":[{"id":0,"value":"# Real-World Data Apps with SQLite Cloud: Part 2\n\nPaste a SQLiteCloud connection connection string in the text field , then check the box below to connect via the JavaScript SDK.\n\n_NOTE:_ You need a SQLiteCloud Connection string to use this notebook.","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":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\n\n_NOTE:_ Each `Session` tests different skills, so some rows may be blank.","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)}...`\n\n- Each `Session` tests different skills, so some columns may be blank\n- Not all `Candidate`s complete every skill, so some rows may be blank","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":442,"value":"$2d","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 = npm('@sqlitecloud/drivers')","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 = new sqlitecloud.Database(sqlite_cloud_conn_str)","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) => project.sql(`USE DATABASE evaluation_sessions; ${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":"$W2e","stdlib":"1","titling":"auto"}} 2c:"$2b:body"