30: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) ;31:[] 2e:{"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"},{"id":706,"value":"van_attempts.map(d => d.toJSON())","pinned":true,"hidden":false,"mode":"ojs"},{"id":687,"value":"van_attempts","pinned":false,"hidden":false,"mode":"ojs"},{"id":666,"value":"select * from organizations","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:mainDB"},{"id":656,"value":"$30","pinned":false,"hidden":false,"mode":"sql","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"},{"id":641,"value":"randomDate(new Date(2024, 1, 8), new Date(2024, 6, 1))","pinned":true,"hidden":false,"mode":"ojs"},{"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","output":"","database":"var:mainDB"},{"id":510,"value":"select * from contact_method_results","pinned":true,"hidden":false,"mode":"sql","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","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","output":"data","database":"var:mainDB"},{"id":398,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"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","output":"randNums","database":"var:emptyDB"},{"id":555,"value":"emptyDB = DuckDBClient.of()","pinned":false,"hidden":false,"mode":"ojs"},{"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"},{"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"},{"id":222,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"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"},{"id":233,"value":"import { SummaryTable } from \"@observablehq/summary-table\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}