2c:null 2d:["$","title",null,{"children":"MOVE TC Data Sources | James Gordon | Observable"}] 32:T561,WITH id_rand_nums AS ( SELECT ROW_NUMBER() OVER () AS id, random() AS _rand_method, random() AS _rand_result, random() AS _rand_organization, random() AS _rand_organizer, random() AS _rand_assessment, random() AS _rand_rating FROM range(4200) ), w_method AS ( SELECT a.*, contacttypename FROM id_rand_nums a, contact_methods b WHERE _rand_method > b._rand_range_min AND _rand_method < b._rand_range_max ), w_result AS ( SELECT a.*, b.resultshortname FROM w_method a LEFT JOIN contact_method_results AS b ON a.contacttypename = b.contacttypename AND _rand_result > b._rand_range_min AND _rand_result < b._rand_range_max ), w_organization AS ( SELECT a.*, b.name AS organization FROM w_result a LEFT JOIN organizations AS b ON _rand_organization > b._rand_range_min AND _rand_organization < b._rand_range_max ), w_organizer AS ( SELECT a.*, b.name AS organizer FROM w_organization a LEFT JOIN organizers AS b ON a.organization = b.organization AND _rand_result > b._rand_range_min AND _rand_result < b._rand_range_max ) -- output SELECT id AS contactscontactid, organization, organizer, contacttypename, resultshortname datecanvassed FROM w_organizer JOIN dates_canvassed USING (id) ;33:[] 30:{"id":"80b5f46bb1c26941","slug":"move-tc-data-sources","title":"MOVE TC Data Sources","thumbnail":"91be6a0e805fb0d5c0c130ce291eaadd0ec0a7af97b02717778f6fb5776843bd","access_level":"public","create_time":"$D2024-01-15T17:26:03.563Z","update_time":"$D2024-01-18T17:38:50.938Z","view_time":null,"first_public_time":"$D2024-01-15T17:26:03.563Z","has_unpublished_edits":null,"fork":{"id":"21d1b3d3e49e8f08","slug":"google-sheets-starter","title":"Google Sheets Starter","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"64b86b1acabda948","login":"gordo","name":"James Gordon","avatar_url":"https://avatars-next.observableusercontent.com/3963f21d2924d4c124c24ee3ac4a90bc46978a749162c4cb4cbab793d4af5f0e","tier":"starter_2024","bio":""},"version":708,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"329cccec2ae600ee","login":"gordo","name":"James Gordon","avatar_url":"https://avatars-next.observableusercontent.com/3963f21d2924d4c124c24ee3ac4a90bc46978a749162c4cb4cbab793d4af5f0e","tier":null,"bio":""}],"members":[{"type":"individual","id":"329cccec2ae600ee","login":"gordo","name":"James Gordon","avatar_url":"https://avatars-next.observableusercontent.com/3963f21d2924d4c124c24ee3ac4a90bc46978a749162c4cb4cbab793d4af5f0e","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"MOVE TC Data Sources","cells":[{"id":0,"value":"# MOVE TC Data Sources\n\nA utility notebook for querying various data sources related to MOVE's Transformative Conversations Program","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":706,"value":"van_attempts.map(d => d.toJSON())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":687,"value":"van_attempts","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":666,"value":"select * from organizations","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:mainDB"},{"id":656,"value":"$32","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"van_attempts","database":"var:mainDB"},{"id":645,"value":"datesCanvassed = Array.from(Array(4200).keys()).map(k => ({id: k, datecanvassed: randomDate(new Date(2024, 1, 8), new Date(2024, 6, 1))}))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":641,"value":"randomDate(new Date(2024, 1, 8), new Date(2024, 6, 1))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":502,"value":"WITH join_cm AS (\n SELECT\n id,\n _rand_result,\n contacttypename\n FROM\n rand_nums, contact_methods cm\n WHERE\n _rand_method > cm._rand_range_min \n AND\n _rand_method < cm._rand_range_max\n )\nSELECT\n join_cm.id,\n join_cm.contacttypename,\n resultshortname\nFROM\n join_cm\nLEFT JOIN\n contact_method_results AS cmr\nON\n join_cm.contacttypename = cmr.contacttypename\nAND\n _rand_result > cmr._rand_range_min \nAND\n _rand_result < cmr._rand_range_max\nORDER BY\n id","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:mainDB"},{"id":510,"value":"select * from contact_method_results","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:db"},{"id":537,"value":" SELECT\n a.contacttypename,\n a._rand_range_min AS _method_range_min,\n a._rand_range_max AS _method_range_max,\n b.resultshortname,\n b._rand_range_min AS _result_range_min,\n b._rand_range_max AS _result_range_max\n FROM\n contact_methods AS a\n JOIN\n contact_method_results AS b\n USING (\n contacttypename\n )","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:mainDB"},{"id":487,"value":"WITH cmr AS (\n SELECT\n a.contacttypename,\n a._rand_range_min AS _method_range_min,\n a._rand_range_max AS _method_range_max,\n b.resultshortname,\n b._rand_range_min AS _result_range_min,\n b._rand_range_max AS _result_range_max\n FROM\n contact_methods AS a\n JOIN\n contact_method_results AS b\n USING (\n contacttypename\n )\n)\nSELECT\n rand_nums.*,\n cmr.*,\n _rand_method > cmr._method_range_min AS _rand_gt_method_min,\n _rand_method < cmr._method_range_max AS _rand_gt_method_max,\n _rand_result > cmr._result_range_min AS _rand_gt_result_min,\n _rand_result < cmr._result_range_min AS _rand_gt_result_max\nFROM\n rand_nums, cmr\nWHERE\n -- _rand_method > cmr._method_range_min -- 96,720\n _rand_method > cmr._method_range_min \n-- AND\n -- _rand_method < cmr._method_range_max -- 71,250\n -- _rand_method > cmr._method_range_max\n-- AND\n-- _rand_result > cmr._result_range_min -- 16,811\n-- AND\n-- _rand_result < cmr._result_range_max -- 104,989\n;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"data","database":"var:mainDB"},{"id":398,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":564,"value":"SELECT\n ROW_NUMBER() OVER () AS id,\n random() AS _rand_method,\n random() AS _rand_result\nFROM\n range(4200)","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"randNums","database":"var:emptyDB"},{"id":555,"value":"emptyDB = DuckDBClient.of()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":639,"value":"function randomDate(start, end) {\n return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":340,"value":"mainDB = DuckDBClient.of({\n organizations: await d3.csv(getCsvUrl(458593155), d3.autoType),\n organizers: await d3.csv(getCsvUrl(302735489), d3.autoType),\n assessments: await d3.csv(getCsvUrl(0), d3.autoType),\n assessments_sqr_mapping: await d3.csv(getCsvUrl(1728706480), d3.autoType),\n contact_method_results: await d3.csv(getCsvUrl(1668346630), d3.autoType),\n contact_methods: await d3.csv(getCsvUrl(27227511), d3.autoType),\n rand_nums: randNums,\n dates_canvassed: datesCanvassed\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":222,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":187,"value":"// Converts a Google Sheets website URL to its CSV URL. You can also go to “File → Publish to web”, select the “Comma-separated values (.csv)” type, select the sheet with your data, and use that CSV URL directly with `d3.csv` above. If you need data to update faster, try the Google Sheets API: https://stackoverflow.com/questions/30082277/accessing-a-new-style-public-google-sheet-as-json/44479726#44479726\n\ngetCsvUrl = gid => {\n const id = \"13G0JggN51mKljuLy1jq9EFuorzFesALzGPWirHUHcMc\"\n return `https://docs.google.com/spreadsheets/d/${id}/export?format=csv&gid=${gid}`\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":233,"value":"import { SummaryTable } from \"@observablehq/summary-table\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1"}} 31:"$30:body"