[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_365":366,"_367":368},"routes/_app.x.$handle.$name",{"_104":103,"_32":44,"_113":90,"_27":114,"_13":14,"_115":-5,"_11":116,"_117":118,"_119":120,"_121":122,"_123":124,"_20":-5,"_125":126,"_127":128,"_129":25,"_130":131,"_132":58,"_133":-5,"_134":-5,"_135":136},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_42":43,"_32":44,"_37":45,"_46":-5,"_47":25,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":56,"_57":58,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":63,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":25},"version",103,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":20,"_34":36,"_37":38,"_39":14,"_40":41,"_24":25},"previous",{"_30":31,"_32":28,"_33":20,"_34":35},"deleted",false,"deletedHere","path","about.md","latestPath","fileId","582df2fc-fd32-11ef-95db-569c3dd06744","name","fileType","content","## Alex Wein's web site\n\nWelcome to a web site. I live in Oakland, California\nand do business intelligence for a software company.\nPersonal web sites are good things to have.\n\nI'm on [Bluesky](https://bsky.app/profile/alexwein.bsky.social).","## Alex Wein's web site\n\nThank you for finding my personal website. I live in Oakland, California\nand work as an analytics engineer for a software company.\nI sometimes make little word games; here's one called [Gord Wame](https://gordwame.val.run/play/pbesoans).\n\n\nI'm on [Bluesky](https://bsky.app/profile/alexwein.bsky.social).","createdAt",["D",1769397462851],"branchVersion","branchId","ddc5a5ea-fcf1-11ef-8cc6-569c3dd06744","id","ddc30ef2-fcf1-11ef-8cc6-569c3dd06744","alexBlogs",["D",1741530120955],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","me writing about things maybe","imageUrl","b85dcfbf-50f1-466c-9e2b-28ac90e0dd23","pinnedFileId","955d7b6e-1855-4b84-a436-6ce9b2f43845","pinPreview",true,"pinMigrated","screenshotVersion",82,"likeCount",0,"orgId","875ed3cd-ae4b-40bb-a45f-6a1970e11689","tursoName","ddc30ef2fcf111ef8cc6569c3dd06744","tursoHostname","ddc30ef2fcf111ef8cc6569c3dd06744-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789513278143],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_104":103,"_112":108},"org",{"_104":103,"_105":106,"_107":108,"_18":54,"_109":110,"_111":25},"branch",{"_13":89,"_32":90,"_42":41,"_91":43,"_92":93,"_94":95,"_96":49,"_97":98,"_99":100,"_101":44,"_102":103},"isOwned",113,"main","projectId","forkedBranchId","8e73c264-d835-11ef-bbbf-e6cdfca9ef9f","forkedBranchVersion",159,"forkedProjectPrivacy","forkedProjectId","8e7382ae-d835-11ef-bbbf-e6cdfca9ef9f","forkedProjectOrgId","e81e7335-6d4c-4764-a902-d0962bbfa9f7","projectName","userHandle","alexwein","handle","displayName","Alex Wein","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ycjh6emNHdk9mMnY5djU3OFZBbEdleGhQZFAifQ","tier","pro","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_42":43,"_32":44,"_37":360,"_46":-5,"_47":25,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":56,"_57":58,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":361,"_72":63,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":362,"_84":363,"_86":364,"_88":25},"tree",[225,228,238,239,240,241,242,243],"files",[225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243],"counts",{"_214":63,"_215":216,"_217":187,"_218":219,"_220":221},"contents",[150,151,152,153,154,155,156,157],"upstreamProject",{"_42":98,"_32":145,"_51":146,"_53":147,"_48":49,"_82":148},"upstreamBranch",{"_42":93,"_37":138,"_139":140,"_32":90,"_91":98,"_13":141,"_92":-5,"_94":-5,"_142":143,"_46":-5,"_88":25,"_48":49,"_102":144,"_64":100,"_101":145},"emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{"_56":137},"alex-we.in",["D",1737491000655],"updatedAt",["D",1744030679667],167,"lockfileKey","1767103399424-esm.town_v_stevekrouse_markdownBlogStarter@167-main","stevekrouse","markdownBlogStarter","Starter template for a markdown blog","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/44a31e5c-3e8a-48a8-d067-5785c0b08200/square512?exp=1789577999&sig=ee63c4c289535e1ec3ec3985a552b540a166eb8a40980151f34317c1fedce995",{"_104":144,"_112":149},"https://images.clerk.dev/uploaded/img_2PqHa2Gsy93xQrjh2w78Xu0cChW.jpeg",{"_42":209,"_32":210,"_30":211,"_161":-5,"_40":41,"_39":61,"_163":58,"_37":212,"_13":63,"_33":207,"_34":-5,"_24":25,"_166":-5,"_27":213},{"_42":203,"_32":204,"_30":205,"_161":-5,"_40":41,"_39":187,"_163":58,"_37":206,"_13":63,"_33":207,"_34":-5,"_24":25,"_166":-5,"_27":208},{"_42":195,"_32":196,"_30":197,"_161":-5,"_40":41,"_39":198,"_163":58,"_37":199,"_13":200,"_33":182,"_34":201,"_24":25,"_166":-5,"_27":202},{"_42":189,"_32":190,"_30":191,"_161":-5,"_40":41,"_39":187,"_163":25,"_37":192,"_13":63,"_33":20,"_34":193,"_24":25,"_166":-5,"_27":194},{"_42":185,"_32":28,"_30":31,"_161":-5,"_40":41,"_39":14,"_163":58,"_37":186,"_13":187,"_33":20,"_34":36,"_24":25,"_166":-5,"_27":188},{"_42":176,"_32":177,"_30":178,"_161":-5,"_40":41,"_39":179,"_163":58,"_37":180,"_13":181,"_33":182,"_34":183,"_24":25,"_166":-5,"_27":184},{"_42":168,"_32":169,"_30":56,"_161":-5,"_40":41,"_39":170,"_163":25,"_37":171,"_13":172,"_33":173,"_34":174,"_24":25,"_166":-5,"_27":175},{"_42":158,"_32":159,"_30":160,"_161":-5,"_40":41,"_39":162,"_163":58,"_37":164,"_13":63,"_33":20,"_34":165,"_24":25,"_166":-5,"_27":167},"e8325a3e-01b8-11f0-afc4-569c3dd06744","robots.txt","e8325aca-01b8-11f0-afc4-569c3dd06744","parentId",63,"latest",["D",1742055412962],"User-agent: *\nAllow: /\n\n# Cribbed from macwright.org who cribbed from ifixit\nUser-agent: Amazonbot\nDisallow: /\nUser-agent: anthropic-ai\nDisallow: /\nUser-agent: Applebot-Extended\nDisallow: /\nUser-agent: AwarioRssBot\nDisallow: /\nUser-agent: AwarioSmartBot\nDisallow: /\nUser-agent: Bytespider\nDisallow: /\nUser-agent: CCBot\nDisallow: /\nUser-agent: ClaudeBot\nDisallow: /\nUser-agent: Claude-Web\nDisallow: /\nUser-agent: cohere-ai\nDisallow: /\nUser-agent: DataForSeoBot\nDisallow: /\nUser-agent: Diffbot\nDisallow: /\nUser-agent: FacebookBot\nDisallow: /\nUser-agent: Google-Extended\nDisallow: /\nUser-agent: GPTBot\nDisallow: /\nUser-agent: magpie-crawler\nDisallow: /\nUser-agent: NewsNow\nDisallow: /\nUser-agent: news-please\nDisallow: /\nUser-agent: omgili\nDisallow: /\nUser-agent: omgilibot\nDisallow: /\nUser-agent: peer39_crawler\nDisallow: /\nUser-agent: peer39_crawler/1.0\nDisallow: /\nUser-agent: PerplexityBot\nDisallow: /\nUser-agent: Scrapy\nDisallow: /\nUser-agent: TurnitinBot\nDisallow: /","label",[159],"d00f7e12-ea9e-11f0-b7e8-42dde27851f2","index.tsx",99,["D",1767662826471],28,"http","/** @jsxImportSource https://esm.sh/react@18.2.0 */\nimport Markdown from \"https://esm.sh/react-markdown@9?deps=react@18.2.0\";\nimport remarkGfm from \"https://esm.sh/remark-gfm?deps=react@18.2.0\";\nimport { listFiles, readFile } from \"https://esm.town/v/std/utils@79-main/index.ts\";\nimport { renderToString } from \"npm:react-dom@18.2.0/server\";\nimport { getHits } from \"./analytics.ts\";\nimport { Layout } from \"./Layout.tsx\";\n\nfunction Header() {\n return (\n

\n Home\n About\n

\n );\n}\n\nfunction PostComponent({ markdown, link }: { markdown: string; link?: string }) {\n return (\n

\n {link}\n {markdown}\n

\n );\n}\n\nexport default async function(req: Request): Promise {\n const url = new URL(req.url);\n const referer = req.headers.get(\"referer\");\n let monthHits, todayHits;\n try {\n const hits = await getHits(url.pathname, referer);\n monthHits = hits.monthHits;\n todayHits = hits.todayHits;\n } catch (e) {\n console.log(\"Caught database error: \", e);\n }\n if (url.pathname === \"/\") {\n const files = await listFiles(import.meta.url);\n const posts = files.filter((file) => file.path.startsWith(\"posts/\"));\n const postContents = await Promise.all(\n posts.map(async (file) => readFile(file.path, import.meta.url)),\n );\n\n return html(\n

Alex Wein has some thoughts

\n {postContents.map((markdown, i) => )}\n View source\n

,\n );\n }\n if (url.pathname.startsWith(\"/posts/\")) {\n const markdown = await readFile(url.pathname, import.meta.url);\n return html(\n

\n {markdown}\n

,\n );\n }\n const aboutPaths = [\"/about\", \"/about/\"];\n if (aboutPaths.includes(url.pathname)) {\n const markdown = await readFile(\"/about.md\", import.meta.url);\n return html(\n

\n {markdown}\n

,\n );\n }\n\nconst visualizerPaths = [\"/2019/03/15/visualizing-visualizers/\", \"/2019/03/15/visualizing-visualizers\"];\n if (visualizerPaths.includes(url.pathname)) {\n\n const markdown = await readFile(\"/other-posts/dvs-spin.md\", import.meta.url);\n const response = await fetch(\n \"https://raw.githubusercontent.com/a-lexwein/weblog/refs/heads/master/public/dvs-fragment.html\",\n );\n const fragmentText = await response.text();\n\n const style = `\n body {\n background-color: #E5E5E5;\n }\n svg {\n animation-name: spin;\n animation-duration: 7000ms;\n animation-iteration-count: infinite;\n animation-timing-function: linear;\n transform-origin: 400px 315px;\n }\n @keyframes spin {\n 0% {\n transform: rotate(0deg);\n }\n 16.66% {\n transform: rotate(-120deg); \n }\n 33.32% {\n transform: rotate(-120deg); \n } \n 49.98% {\n transform: rotate(-240deg); \n } \n 66.64% {\n transform: rotate(-240deg); \n } \n 83.3% {\n transform: rotate(-360deg); \n } \n 100% {\n transform: rotate(-360deg);\n }\n }\n `;\n\n const markdownText = renderToString({markdown});\n const fullHtml = `\n \n \n \n \n \n SVG Animation\n \n \n \n ${fragmentText}\n ${markdownText}\n \n \n \n `;\n return new Response(fullHtml, {\n headers: {\n \"content-type\": \"text/html\",\n },\n });\n }\n \n if (url.pathname === \"/dbt\") {\n const markdown = await readFile(\"/other-posts/dbt-cheatsheet.md\", import.meta.url);\n return html(\n

\n {markdown}\n

,\n );\n }\n\n if (url.pathname === \"/robots.txt\") {\n const robots = await readFile(\"/robots.txt\", import.meta.url);\n return new Response(robots, {\n headers: {\n \"content-type\": \"text/plain\",\n },\n });\n }\n\n return new Response(\"Not found\", { status: 404 });\n}\n\nfunction html(children: React.ReactNode) {\n return new Response(\n renderToString(\n \n {children}\n ,\n ),\n {\n headers: {\n \"content-type\": \"text/html;\",\n },\n },\n );\n}",[169],"3c6fde74-9c8b-11f0-890d-0224a6c84d84","analytics.ts","322a8236-0085-11f0-be44-569c3dd06744",91,["D",1759078227670],9,"script","// inspired by steve's implementation https://www.val.town/x/stevekrouse/stevekrouse_dot_com/branch/main/version/26/code/analytics.ts\n// import { sqlite } from \"https://esm.town/v/std/sqlite?v=5\";\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite/main.tsx\";\n\nexport async function getHits(url, referer) {\n await sqlite.batch([\n \"CREATE TABLE IF NOT EXISTS blog_hits (id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT, referer TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)\",\n { sql: `INSERT INTO blog_hits (url, referer) VALUES (:url, :referer)`, args: { url, referer } },\n // \"SELECT COUNT(*) FROM blog_hits WHERE strftime('%m', timestamp) = strftime('%m', 'now') AND strftime('%Y', timestamp) = strftime('%Y', 'now')\",\n // \"SELECT COUNT(*) from blog_hits WHERE strftime('%m', timestamp) = strftime('%m', 'now') AND strftime('%Y', timestamp) = strftime('%Y', 'now') AND strftime('%d', timestamp) = strftime('%d', 'now')\",\n ]);\n return {};\n}",[177],"93e86b52-fa65-11f0-9a75-42dde27851f2",["D",1769397462851],1,[28],"ddc6f17a-fcf1-11ef-8cc6-569c3dd06744","README.md","9036cd94-cec1-4ab2-a08b-5cc0afc206d4",["D",1741530120955],"# myBlog\n\nThis is a simple static blog.\n\nPosts go in the /posts directory.",[190],"aaa894ec-fd2d-11ef-aff3-569c3dd06744","Layout.tsx","032f82af-0a67-4a3a-910b-5e06133325ca",54,["D",1741555805021],2,"/** @jsxImportSource npm:react@18.2.0 */\nimport type { ReactNode } from \"npm:react@18.2.0\";\n\nexport function Layout({ children }: { children: ReactNode }) {\n return (\n \n \n \n \n Alex Whines\n \n \n \n {children}\n \n \n );\n}",[196],"ddc6d5dc-fcf1-11ef-8cc6-569c3dd06744","posts","6533e42b-8425-47bb-96b7-d8a39a081656",["D",1741530120955],"directory",[204],"7e7b573e-2946-11f0-baff-569c3dd06744","other-posts","91476a43-ba52-4f50-84b6-24f522b733d9",["D",1746404319453],[210],"pulls","branches",10,"forks","envVars",{"_222":187,"_224":63},"connections",{"_222":63,"_223":63},"count","unfilledCount","emptyCount",{"_42":209,"_32":210,"_30":211,"_161":-5,"_40":41,"_39":61,"_163":58,"_37":357,"_13":63,"_33":207,"_34":-5,"_24":25,"_166":-5,"_245":358,"_27":359},{"_42":349,"_32":350,"_30":351,"_161":211,"_40":41,"_39":352,"_163":58,"_37":353,"_13":293,"_33":20,"_34":354,"_24":25,"_166":-5,"_245":355,"_27":356},{"_42":342,"_32":343,"_30":344,"_161":211,"_40":41,"_39":61,"_163":58,"_37":345,"_13":63,"_33":20,"_34":346,"_24":25,"_166":-5,"_245":347,"_27":348},{"_42":203,"_32":204,"_30":205,"_161":-5,"_40":41,"_39":187,"_163":58,"_37":339,"_13":63,"_33":207,"_34":-5,"_24":25,"_166":-5,"_245":340,"_27":341},{"_42":330,"_32":331,"_30":332,"_161":205,"_40":41,"_39":333,"_163":58,"_37":334,"_13":335,"_33":20,"_34":336,"_24":25,"_166":-5,"_245":337,"_27":338},{"_42":322,"_32":323,"_30":324,"_161":205,"_40":41,"_39":325,"_163":58,"_37":326,"_13":293,"_33":20,"_34":327,"_24":25,"_166":-5,"_245":328,"_27":329},{"_42":313,"_32":314,"_30":315,"_161":205,"_40":41,"_39":316,"_163":58,"_37":317,"_13":318,"_33":20,"_34":319,"_24":25,"_166":-5,"_245":320,"_27":321},{"_42":305,"_32":306,"_30":307,"_161":205,"_40":41,"_39":308,"_163":58,"_37":309,"_13":200,"_33":20,"_34":310,"_24":25,"_166":-5,"_245":311,"_27":312},{"_42":297,"_32":298,"_30":299,"_161":205,"_40":41,"_39":300,"_163":58,"_37":301,"_13":187,"_33":20,"_34":302,"_24":25,"_166":-5,"_245":303,"_27":304},{"_42":288,"_32":289,"_30":290,"_161":205,"_40":41,"_39":291,"_163":58,"_37":292,"_13":293,"_33":20,"_34":294,"_24":25,"_166":-5,"_245":295,"_27":296},{"_42":279,"_32":280,"_30":281,"_161":205,"_40":41,"_39":282,"_163":58,"_37":283,"_13":284,"_33":20,"_34":285,"_24":25,"_166":-5,"_245":286,"_27":287},{"_42":271,"_32":272,"_30":273,"_161":205,"_40":41,"_39":274,"_163":58,"_37":275,"_13":187,"_33":20,"_34":276,"_24":25,"_166":-5,"_245":277,"_27":278},{"_42":263,"_32":264,"_30":265,"_161":205,"_40":41,"_39":266,"_163":25,"_37":267,"_13":200,"_33":20,"_34":268,"_24":25,"_166":-5,"_245":269,"_27":270},{"_42":195,"_32":196,"_30":197,"_161":-5,"_40":41,"_39":198,"_163":58,"_37":260,"_13":200,"_33":182,"_34":201,"_24":25,"_166":-5,"_245":261,"_27":262},{"_42":189,"_32":190,"_30":191,"_161":-5,"_40":41,"_39":187,"_163":25,"_37":257,"_13":63,"_33":20,"_34":193,"_24":25,"_166":-5,"_245":258,"_27":259},{"_42":185,"_32":28,"_30":31,"_161":-5,"_40":41,"_39":14,"_163":58,"_37":254,"_13":187,"_33":20,"_34":36,"_24":25,"_166":-5,"_245":255,"_27":256},{"_42":176,"_32":177,"_30":178,"_161":-5,"_40":41,"_39":179,"_163":58,"_37":251,"_13":181,"_33":182,"_34":183,"_24":25,"_166":-5,"_245":252,"_27":253},{"_42":168,"_32":169,"_30":56,"_161":-5,"_40":41,"_39":170,"_163":25,"_37":248,"_13":172,"_33":173,"_34":174,"_24":25,"_166":-5,"_245":249,"_27":250},{"_42":158,"_32":159,"_30":160,"_161":-5,"_40":41,"_39":162,"_163":58,"_37":244,"_13":63,"_33":20,"_34":165,"_24":25,"_166":-5,"_245":246,"_27":247},["D",1742055412962],"children",[],[159],["D",1767662826471],[],[169],["D",1759078227670],[],[177],["D",1769397462851],[],[28],["D",1741530120955],[],[190],["D",1741555805021],[],[196],"ca7519a4-ecbe-11f0-870f-42dde27851f2","2025-12-25-chart-games-2025.md","c7d41bce-e1c8-11f0-942d-42dde27851f2",100,["D",1767896463296],"## The Two Best Data Visualization-Centric Digital Games of 2025\n\nThere have been video games with great uses of data visualization before, but before this year,\nI don't think I'd ever played a game that was **Really About The Charts**.\nAnd this year I played two! And both were kind of awesome in very different ways.\n\n### [Pocket Boss](https://pocketboss.ch/)\n\nMostly by default, Mario von Rickenbach has made the definitive piece of art about working in business intelligence.\n*Pocket Boss* is a mobile game. You play as a data worker, remotely communicating with your boss through chat DMs.\nEach level is a chart your boss needs you to clean up.\n\n![5509C048-FB3C-410B-AD5C-FA26E60D191B_1_105_c.jpeg](https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/40002f3a-1294-4e73-4283-eaa867020000/public)\n\nGameplay wise, it has a bit in common with some kids apps I love like Windowsill and Sago Mini. To make the bars on the chart \"go up\" or whatever your current objective is, you tap or drag or swipe. It's tactilely satisfying with just a splash of puzzling.\n\nBut the reason to the play the game is for the writing, which I found very funny and **shockingly relatable**.\nAlthough all my managers have been wonderful and open-minded people, in my career in business intelligence,\nit's hard to totally escape the feeling that the numbers don't matter,\nwe're just making pretty pictures to justify whatever decision management was going to make anyways.\n*Pocket Boss* deals with these existential questions of the data worker with wit and a sense of play.\n\n### [Alphadots](https://www.bloomberg.com/games/alphadots)\n\nThere were even more new entrants into the little daily word game racket in 2025.\nBloomberg launched their Games section in September that included *Alphadots*, created by crossword constructor Jeff Chen.\nYou are presented with a crossword-style question mark clue. And the letters are displayed in a line chart, ordered alphabetically along the Y-axis. Hints let you add horizontal baselines.\n\n![345DFBCD-2E4C-419E-B141-320528C9121F_1_105_c.jpeg](https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/ea9a172e-739c-40ba-9321-4000807f7600/public)\n\nIt doesn't have anything to do its chart based nature, but what really keeps me coming back are the consistently solid \"?\" clues. A light, satisfying bit of wordplay every day with a novel solving mechanic.\nI can't remember another word game I've played where alphabetical order played any role.\nIt adds a unique texture to which puzzles are easy or heard: As are always easy to identify, repeating letters are also crucial information, and most of the your time is spent wondering if those dots in the middle are Ls, Ms, or Ns\n\nAnd it's really a chart game! Unlike *Pocket Boss*, *Alphadots* actually actually asks you to interpret some data. You start with a chart. It might take a little time to decode, but the chart has all the information you need solve the puzzle. It's a good time.",[],[204,264],"535ae172-f9fd-11f0-bdc8-42dde27851f2","2025-12-24-five-friendly-sql-tips.md","40602a6c-e122-11f0-9f88-42dde27851f2",101,["D",1769352686869],"## [Five Cool Little Bits of \"Friendly\" SQL for wrangling data in DuckDB (or Snowflake)](https://observablehq.com/@a-lexwein/five-cool-little-bits-of-friendly-sql)\nIn a attempt to get out of my not-blogging rut,\nI wrote a little SQL Tips and Tricks post, focusing on some \"friendly\" additions to the query syntax that DuckDB has introduced over the last few years and Snowflake has adopted as well.\n\nI cover the following keywords.\n\n1. `group by all`\n2. terminal commas (this is not a keyword)\n3. `count_if`\n4. `sample`\n5. `values`\n\nSo that I could use interactive queries, I wrote it as an Observable Notebook, the fantastic Javascript focused live coding platform where DuckDB comes standard.\nIn addition to the tips, I've smuggled in a few opinions about SQL style. Check it out.\n\n### https://observablehq.com/@a-lexwein/five-cool-little-bits-of-friendly-sql",[],[204,272],"9ac16b8c-de8f-11f0-913e-42dde27851f2","2025-04-06-comics-about-comics.md","4b13cbcc-1304-11f0-b814-569c3dd06744",95,["D",1766336880643],5,"## The next addition to your data viz bookshelf should be a Comic About Comics.\n\n![IMG_0626.jpg](https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/03100f91-56e9-400c-96d9-e7c9d3b7c200/public \"A photograph of the books in this post\")\n\nThere are many wonderful books on/of data visualization, and if you work in data,\nyou probably have a few (hopefully more than just *that one*). You should probably get more.\n\nAs your collection grows, consider that the next great book to inspire the chartmaker in you --\nto further you on your journey to master the craft -- may not be a treatise on chart types or data storytelling,\nbut something possibly even cooler: **A Comic About Comics**.\n\nThese are just the ones I own. You really can't go wrong with any of them.\nAnd even if you have no interest in data viz, this Comics About Comics genre is still a fantastic mind-expanded set of books.\nThis books are really special: they're formally inventive but personal and emotional in their own ways.\nThey provide a wonderful set of boxes to think outside of.\n\n### Scott McCloud's *Understanding Comics* ~~Trilogy~~ Ouvre\n\nI discovered Scott McCloud's work in 2018,\nwhen he was interviewed for [an episode of the design podcast 99% Invisible.](https://99percentinvisible.org/episode/speech-bubbles-understanding-comics-scott-mccloud/)\n\nThat conversation -- but especially his 1993 classic *Understanding Comics* which I read right afterwards -- completely blew my mind.\nThere have been less than a handful of times in my career where I felt that lightbulb above my head go on with anywhere near the wattage of when I digested *Understanding Comics*. \n\nI walked away from *Understanding Comics* with this incredible insight that data visualization was really a form of comics\n(\"sequential art\" if you're worried the person you're talking too thinks \"comics\" is a dirty word).\nCharts -- and especially sequences of charts to make things like dashboards or powerpoint presentations -- very definitely count per McCloud's definition:\n\n> Juxtaposed pictorial and other images in deliberate sequence, intended to convey information and/or to produce an aesthetic response in the viewer.\n\nAt that point in 2018, I had been a data person for several years, always making charts to understand and communicate things both professionally and recreationally for many years.\nI had some successes: charts that helped my colleagues make decisions, charts that made cool nerds on tumblr chuckle, even a few that I didn't think were hideous.\nI didn't yet have a full bookshelf of data viz books, but I had more than one (the first I bought was indeed *that one*).\nDespite not having much academic training in it, I felt like I understood visualization pretty well at that point, but *Understanding Comics*\nmade clear how limited by understanding was (and still is).\nEven more so than the grammar of graphics: to me personally this idea of **Charts Are Comics** has been the most powerful aha moment in my \"career\" as a chartmaker.\n\nIf you've read *Understanding Comics* and got something out of it, I'd encourage you to keep going with McCloud's work.\nAlthough 1996's *Making Comics* did not quite inspire me to start making comics, it has great lessons about composition and visual storytelling.\n2000's *Reinventing Comics* in particular had a lot of resonance for me, as someone who's sequential art (charts) is both made and consumed through screens and computers.\n\nAnd just this week, Scott McCloud released another Comic About Comics: *The Cartoonists Club* with Raina Telgemeier.\nAimed at younger readers, it embeds the most important ideas from *Understanding...* and *Making...*\nand into a narrative about middler schoolers finding their voice and their people.\n*Understanding Comics* never brought me to tears, but I needed a kleenex after finishing *The Cartoonists Club*.\n\nWhile I hadn't considered the relationship between charts and comics until 2018,\nMany others had made the connection and I just wasn't paying attention.\nMcCloud had spoken at the data viz conference Tapestry in 2013,\nand Moritz Stefaner (data viz maker and discusser extroidinaire) interviewed McCloud in 2017 for the [Data Stories podcast](https://datastori.es/102-comics-and-visual-storytelling-with-scott-mccloud/).\nAlso in 2017 in academia, Benjamin Bach worked with Nathalie Henry Riche, Sheelagh Carpendale, and Hanspeter Pfister to publish\n[The Emerging Genre of Data Comics](http://sheelaghcarpendale.ca/innovis/uploads/Publications/Publications/carpendale2017emerging.pdf)\n(which itself is a Comic About Comics!).\nAnd in 2016, comics professor Nick Sousanis gave a [Keynote at Tapestry](https://www.youtube.com/watch?v=7veGaFlu9Xk&t=2698s).\n\n### *Unflattening* by Nick Sousanis\n\nIt is reductive to call *Unflattening* a Comic About Comics: it's definitely a comic,\nbut it's about something a little bigger and a lot more cerebral than McCloud's work (it's a PhD dissertation).\nIt's hard to pin down exactly what to call that (the shapeliness of ideas?).\nThat written words are insufficient to describe the complexity of the topic is another thing the book is kind of about.\n\n*Unflattening* is also a compositional feast: pages flow in all sorts of directions.\nEvery spread maps complex and abstract things to the two-dimensional page in ways that befit the nonlinearity and high dimensionality of those abstract spaces.\nMuch of the hard work of data viz is making invisible abstract things tractable in two dimensions, and *Unflattening* contains lesson after lesson in it.\n\nRJ Andrews deserves a shout-out. RJ recommended Unflattening to me at the Tapestry conference in 2018\n(I gave [my own brief talk](https://www.youtube.com/watch?v=PpAfmTgcGPY) at that Tapestry, invigorated by discovering *Understanding Comics* at the beginning of the year).\nIf you don't have a data viz bookshelf yet,\nI can safely say Andrews's newly remastered [*Info We Trust*](https://visionarypress.com/products/info-we-trust) is the perfect place to start (way better than *that one*).\nRJ's book is of what it's about in the same way all these wonderful Comics About Comics are.\nRJ also has a wonderful write-up that covers this very topic: [\"Books without data for the data professional\"](https://infowetrust.com/project/information-arts)\nthat includes both *Unflattening* and *Understanding Comics*.\n\n### *What It Is* and *Making Comics* by Lynda Barry\n\n*What It Is*, like McCloud's classic, is also about understanding comics, but it's form and content differ dramatically.\nWhile McCloud explicates the conventions of comics with a focus on the prototypical grid of rectangular panels,\nBarry is more interested in asking than answering, and her work is -- gloriously -- too untidy to fit into little boxes.\nEven compared to heady academic work of *Unflattening*, *What It Is* was maybe the most challenging read for me of any of these comics.\nIt's not formless, but it's far from the rectilinearity of most comics I read.\nAll these books are great for leaving on your coffee table, and leafing through random pages, but *What It Is* works particularly well for this.\n\nBarry's *Making Comics* had a magical impact on me: It actually got me drawing!\nI didn't work my way through the whole book, but I tried a several of the exercises.\nAnd for about two months in 2020, I drew a self-portrait on a index card every day (they're hideous and I'm so proud of them).\nBut even if you can't find to courage to draw, *Making Comics* is worth it for how she grounds the craft in modes we all learned as children.\n\nSo read some books, bust out that pen and paper, make some charts, make some comics, make something else.\n\n![IMG_0623.jpg](https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/4a0844b9-a977-47c6-a086-fc949432bf00/public \"From Barry's Making Comics: you don't have to have any artistic skill to do this. you just need to be brave and sincere.\")",[],[204,280],"2d3fabe2-f9ff-11f0-8bd8-42dde27851f2","2025-03-31-old-tumblrs.md","4b13a28c-1304-11f0-b814-569c3dd06744",102,["D",1769353481946],3,"## Some of my better Tumblr posts\n\nAlthough the URL somehow got reshuffled and I have long forgotten the login password, my old Tumblr blog Alex Whines\nis still on the internet. When the tumblr was still active, I kept a list of greatest hits.\nI reproduced the list for the last iteration of my blog, so it was easy enough to bring it over here.\n\nIn addition to all the the fun posts below that I am 98% not ashamed of, my old blog did contain some missteps.\nSorry for the bad opinions and shoddy work!\n\n**Games:**\n- A multi-part analysis of Jeopardy! questions and answers, featuring some fun visualization ([1](http://alexwhines-blog.tumblr.com/post/613905785/helpful-hint-for-when-you-are-a-contestant-on-jeopardy),[2](http://alexwhines-blog.tumblr.com/post/670084704/weve-already-covered-that-whenever-you-dont-know),[3](http://alexwhines-blog.tumblr.com/post/790684867/so-youre-back-on-jeopardy-trebek-is-reading),[4](http://alexwhines-blog.tumblr.com/post/801075069/another-possible-jeopardy-scenario-you-might-as))\n- [Swear words](http://alexwhines-blog.tumblr.com/post/3868278233/scrabble-facts-pt-4) in Scrabble\n- I play a lot of [Boggle](http://alexwhines-blog.tumblr.com/tagged/boggle)\n- Arbitrage [at the boardwalk arcade](http://alexwhines-blog.tumblr.com/post/913999996/hot-investment-tip-boardwalk-arcade-edition)\n\n**Music:**\n\n- [Pitchfork versus Metacritic,](http://alexwhines-blog.tumblr.com/post/809415856/yesterday-something-called-flavorwire-had-a-post) a quantitative analysis, and some more on the [Metacritic data](http://alexwhines-blog.tumblr.com/tagged/metacritic).\n- [A dialogue in song titles](http://alexwhines-blog.tumblr.com/post/1587830624/a-dialogue-in-song-titles-speaker-1-is-in-light)\n- What's the [Scenario](http://alexwhines-blog.tumblr.com/post/2680112431/know-your-scenarios-a-tribe-called-quest)?\n- I also used to maintain a tumblr about [Songs from Treme](https://songsfromtreme-blog.tumblr.com/)\n\n**Nesting and recursion in fun places:**\n\n- One step of the Hokey Pokey is '[do the Hokey Pokey](http://alexwhines-blog.tumblr.com/post/733602356/recursion-in-fun-places-depending-on-how-you)'\n- [Fish fish fish.](http://alexwhines-blog.tumblr.com/post/4619309811/according-to-wikipedia-northern-pike-feed-on)\n- [My idea for a TV show](http://alexwhines-blog.tumblr.com/post/855597954/if-anyone-knows-anyone-at-nbc-ive-got-an-awesome) about 30 Rock and Studio 60\n- If someone was reading The Lion, The Witch, and The Wardrobe in the [fictional universe](http://alexwhines-blog.tumblr.com/post/3094931180/a-thought-continued) of the movie Groundhog Day\n\n**Food:**\n\n- The hole in the [Candy Bar Venn Diagram](http://alexwhines-blog.tumblr.com/post/407296011/hot-investment-tip-candy-bar-edition-hopefully)\n- Diagrams of [Scotch eggs](http://alexwhines-blog.tumblr.com/post/715341118/were-you-aware-of-the-existence-of-scotch-eggs-i) and [Snickers ice cream cones](http://alexwhines-blog.tumblr.com/post/3613407044/the-snickers-ice-cream-cone-lucky-charms)\n- [A guide](http://alexwhines-blog.tumblr.com/post/587595945/the-chinese-spot-across-the-street-from-me-is-only) for price-conscious people who want to buy chicken wings at the intersection of 7th St. and Q St. NW\n- Tale of the Tape: [E.L. Fudge and Double Stuffed E.L. Fudge](http://alexwhines-blog.tumblr.com/post/3374412499/i-made-a-pretty-big-discovery-at-the-supermarket)\n\n**More words:**\n\n- [Moonfish](http://alexwhines-blog.tumblr.com/post/5863557451/selfish-shellfish-sell-fish-shellfish) moon fish.\n- Why *[improbably](http://alexwhines-blog.tumblr.com/post/7824736366/the-word-im-currently-finding-interesting)* is interesting.\n- [Example sentences](http://alexwhines-blog.tumblr.com/post/5655919664/example-sentences) I have produced in school\n- Buffalo people [buffalo people](http://alexwhines-blog.tumblr.com/post/4580679647/buffalo-people-buffalo-people).\n- Hotspots/Potshots, Sleepover/Oversleep, [etc.](http://alexwhines-blog.tumblr.com/post/210515042/this-all-started-when-i-found-headwater-and-missed)\n\n- Just a photograph of [me eating cheese puffs](http://alexwhines-blog.tumblr.com/post/9640074973/a-hypothesis-has-been-floated-that-my-blog-would)",[],[204,289],"3cb8ffae-0f3d-11f0-8d8e-569c3dd06744","2025-03-29-posts-only-a-mother-could-like.md","9db0eb90-0cd7-11f0-8b0c-569c3dd06744",73,["D",1743541613445],"## Posts Only a Mother Could Like\n\nIt's not surprising my Bluesky posts don't get many likes. I don't have many followers and honestly I'm not a great poster.\nI even call myself boring in my bio. With so few likes, every like is meaningful to me.\nEspecially when there's only a single ❤️ on the post.\nI know it's just someone pushing a button, but that little one-to-one connection across cyberspace,\nto say \"I see you, even if nobody else does\", it means something.\n\nBut it means something a little different when that person is your mother.\n\nBelow are the ten posts of mine liked by my mother and nobody else.\n\n| Timestamp | Text |\n|-----------|------|\n| [3/25/2025, 2:16:42 PM](https://bsky.app/profile/alexwein.bsky.social/post/3ll7hjfjewk24) | ranking daily games against funness and the complicity of the publishers is its own knotty puzzle |\n| [3/15/2025, 3:08:07 PM](https://bsky.app/profile/alexwein.bsky.social/post/3lkgfq4tm3k2z) | It seems like all the cool kids are going to this. But also me. |\n| [3/1/2025, 11:55:58 PM](https://bsky.app/profile/alexwein.bsky.social/post/3lje4p4eovk2f) | I forked my last word game and added wikipedia's list of birds alexwein-loopylettersbirds.web.val.run |\n| [2/18/2025, 2:21:10 AM](https://bsky.app/profile/alexwein.bsky.social/post/3lig77ozynk2x) | Weekend word game. Not fun, per se, but more fun than the last time I tried to make this particular game. alexwein-loopyletters.web.val.run |\n| [1/8/2025, 3:13:51 PM](https://bsky.app/profile/alexwein.bsky.social/post/3lfahbmnbnk26) | I got a shout-out for today's Order Up (I submitted something worse and Adam ran with this instead). You're all doing Order Up every day, right? orderup.games |\n| [1/2/2025, 4:19:17 PM](https://bsky.app/profile/alexwein.bsky.social/post/3leri53kqsk2z) | welp...I now have two half-written blog posts. Here's the list though. All definitely word games for sure. |\n| [12/13/2024, 12:57:47 AM](https://bsky.app/profile/alexwein.bsky.social/post/3ld5lcvqyk22w) | I've never had it to drink (so didn't include it) but have always liked the Lil Wayne song \"D'usse\" www.youtube.com/watch?v=TS-h... |\n| [12/12/2024, 9:41:03 PM](https://bsky.app/profile/alexwein.bsky.social/post/3ld5ad4wt3s2e) | My sister's book club needed to know the longest sentence in Henry James's The Golden Bowl. Probably too naive a methodology, but the results seem plausible observablehq.com/@a-lexwein/l... |\n| [12/7/2024, 3:07:33 PM](https://bsky.app/profile/alexwein.bsky.social/post/3lcpxyvnd4k2m) | i'm having a very strong showing so far in december's monthly boggle tournament |\n| [11/8/2024, 4:39:12 PM](https://bsky.app/profile/alexwein.bsky.social/post/3lah7m34lem27) | toying with the idea of accomplishing more than zero tasks today |\n\n[Here's the val](https://www.val.town/v/alexwein/bskySoloLikes) where I pull the data. And [here's a full list](https://www.val.town/v/alexwein/soloLikesDisplay) my posts that have been liked by exactly one person.\n\nI love you too, mom! ❤️",[],[204,298],"b9775a52-5d37-11f0-97bb-0224a6c84d84","2025-03-14-pie-charts-why-not.md","0aaf2610-00ff-11f0-84a4-569c3dd06744",84,["D",1752115436458],"## A good reason not to use pie charts\n\nHere is a compelling reason not use pie charts when visualizing data:\n\nOne of your stakeholders has an MBA and has read exactly one book on data visualization (you know which one).\nHe thinks pie charts are oh so terrible, and if your reports and dashboards contain pie charts,\ninstead of focusing on the data being communicated, he will complain about the pie charts.\nBecause your goal as somebody making charts is to faciliate understanding, avoiding pie charts altogether\ncan actually be a smart strategy.\nFor all their merits, you never strictly \"need\" pies (or any one chart type).\nIf a chart's form would be a distraction from the data,\nit is maybe not the the right chart.\n\n## A good reason to use pie charts\n\nFuck that guy.",[],[204,306],"a63055d4-0da5-11f0-bca9-569c3dd06744","2025-03-09-songbot.md","2ff93356-fd1d-11ef-8f04-569c3dd06744",72,["D",1743366555732],7,"## I made a Bluesky bot\n\n[mynewfavoritesong.bluesky.social](https://bsky.app/profile/mynewfavoritesong.bsky.social)\n\nWhen I signed up for Val Town, I had a first project in mind.\nWhile Spotify's algorithms do a serviceable job notifying me of new music by artists I like, it's not really possible to \"follow\" a songwriter.\nHelpfully, Spotify maintains *Written By...* playlists for many songwriters.\nHere's one for [Jonnali Mikaela \"Noonie Bao\" Parmenius](https://open.spotify.com/playlist/37i9dQZF1EFMc5WcffBKFm?si=5d6294f36acf48f8),\nwho has co-writing credits on two of my absolute favorite pop songs of the last 15 years.\n\nBut these first-party playlists aren't in chronological order,\nand I don't know of a good way being notified when songs get added.\nSo it's quite hard to make sure I don't miss out on the latest credit from Sabrina Carpenter's go-to [Amy Allen](https://open.spotify.com/playlist/37i9dQZF1EFAW2Wx2Q7Fjt?si=19a4c0d77645412f).\n\nSo the plan was to build a cron job val that used the Spotify API to poll a playlist on a regular basis, and check whether anything had changed since the last time,\nand then create some sort of alert upon detection of a change.\nIt seemed buildable, even with my modest dev skills.\nI was able to use [some tools](https://www.val.town/v/stevekrouse/spotifyTracks) that [Steve](https://www.val.town/u/stevekrouse) from Val Town built.\nI was ready to request a playlist for the first time, at which point I learned that Spotify somewhat recently enshittified their web API\nsuch that first-party playlists are no longer available.\n\nSo, I'm still looking for a good way to keep up with songwriters.\n\nBut I was able to take the same basic idea and turn in into My New Favorite Song Bot\n(here's [the val with the code](https://www.val.town/v/alexwein/newFavoriteSongBot)).\nIt's the same thing really: a cron val looking for new songs added to a playlist. When it catches something new, it posts to Bluesky.\nI think it's going to break when I add too many songs.\nI also haven't really thought about what happens if I try to add multiple songs at the same time.\n\nBecause another Val Town user named Jonas Merlin had already made a [Bluesky bot template](https://www.val.town/v/jonasmerlin/bluesky_bot_template),\nthe whole process was quite tractable. It's been working so far! Every time I've added a song, the Bluesky post has dutifully showed up.\n\nI'm a little ambivalent about how having a Favorite Songs bot might impact my relationship to music.\nAnd I haven't really decided how good a song needs to be for me to add it to the playlist. Or what consitutes \"new\".\nThere are seven songs so far; I seem to be on a pace of about one song a week.\nAlso Spotify is maybe evil, and even if it isn't, it is still probably bad for the world and I shouldn't use it and you shouldn't either.\nBut I do, and you can listen to the full playlist [here](https://open.spotify.com/playlist/0P2hUZ6RymtSZcUhCii77t?si=c6c20c26fa434f50).",[],[204,314],"3071b3e8-fd1e-11ef-8b1b-569c3dd06744","2025-03-09-sandwich-rules.md","7383958c-fcfd-11ef-b02a-569c3dd06744",49,["D",1741549157530],"## How to Play \"Is It A Sandwich?\":\n\n- **Number of Players:** 2+\n- **Play time:** 1 minute - ?\n- **Ages**: 2+\n\n### The Object of the game\n\nThe object of **\"Is It A Sandwich?\"** is to just sort of have a conversation.\n\n### Beginning the game\n\nDecide who goes first or just start talking.\n\n### A typical round\n\nA player proposes a candidate.\nEvery player begins discussing whether or not that candidate is a sandwich.\nAfter some debate, players make a judgment that **Yes** the candidate is a sandwich\nor **No**, it is not a sandwich. Players are allowed to change their mind as debate unfolds.\nThe round ends when a player proposes a new candidate to debate.\n\n(Note: although the round ends when someone proposes a new candidate, players may revisit the candidates from previous rounds at any point to reopen debate or change their judgment.)\n\n### Scoring\n\n1. Each player starts with zero points.\n2. If every player reaches consensus that the candidate is a sandwich, everybody adds **+0 points** to their score.\n3. If one player successfully convinced another to change their mind, the convincer adds **+0 points** to their score and the convincee loses **-0** points.\n4. If one player points out the logical inconsistency of the judgments of another player, both players add **+0 points**.\n\n### Ending the game\n\nThe game ends when the food arrives. There is no winner. There are no losers.",[],[204,323],"2a3a15ee-1adb-11f0-9c85-569c3dd06744","2025-03-09-future-abandoned-blog.md","e8062ba7-7ca6-4a30-bbbc-4e1f18b7f930",81,["D",1744818905697],16,"## My future abandoned blog\n\n\"I should blog more\" is a thought I have pretty regularly.\nHaving ideas worth sharing and crafting in them into something that others might find interesting has\nalways been a very rewarding experience.\n\nBut the thing is: Writing is really hard.\n\nAnd I'm not feeling too optimistic that it's about to get any easier for me. Or even that I have ideas worth sharing.\nWhich means posts here will be a combination of infrequent and of poor quality.\nBut I'm trying again. Here is my space for blogging.\n\n- I work in business intelligence, but it is unclear if I'm any good at my job. I probably will write about charts and analytics occasionally.\n- I also enjoy playing word games and have dabbled in making a few (they are also of poor quality). I might write about word games.\n- In previous incarnations of my blog, there's been plenty of \"here's a song I like\" or \"here's a show I watched\". That might happen, too.\n\nYou should probably blog more, too! Writing time is time well spent.\n\n(This site was built in Val Town. Thanks to [Steve](https://www.val.town/x/stevekrouse/markdownBlogStarter) from Val Town for getting me started.)",[],[204,331],["D",1741530120955],[229,230,231,232,233,234,235,236,237],[204],"7e7b5054-2946-11f0-baff-569c3dd06744","dvs-spin.md","7e7b50fe-2946-11f0-baff-569c3dd06744",["D",1746404319453],"# Visualizing The Visualizers\n\n(2025-03-29: This was an old post from 2019 on my previous blog. I noticed some 404 traffic from DVS and figured it was worth hosting on this iteration of the blog)\n\n\"The purpose of visualization is insight, not pictures\" went the headline of [a great interview](https://medium.com/multiple-views-visualization-research-explained/the-purpose-of-visualization-is-insight-not-pictures-an-interview-with-visualization-pioneer-ben-beb15b2d8e9b) with Ben Schneiderman.\n\nTo that I say: \"Usually.\"\n\nNot this chart though. I made this chart -- plotting how members of the Data Visualization Society (including me!) self assess their skills in three dimensions -- with several purposes in mind, and they honestly had very little to do with \"insight\". Instead my goals were the following:\n\n1. Make something attention-grabbing that would stand out among dozens of charts of the same data.\n1. Play with some new-to-me tools and techniques.\n1. Make something aesthetically pleasing.\n1. Get a blog post out of it.\n\nInsights are great. But other stuff is good too!\n\n## How I made the chart\n\n1. The data comes from the [sign-up survey](https://docs.google.com/forms/d/e/1FAIpQLScfivalT2ma3iJZNT8x3hGBJwniWS2rK_Czacnmnj6q1ShlIQ/viewform) for the Data Visualization Society. (If you're into data viz, regardless of experience -- join us!) The data was shared with everyone who joined the DVS, many of whom are also be trying to chart this data.\n1. The actual data work was done in R. I then made a quantile dot plot for the distributions of each of the three survey categories. Matthew Kay showed off some quantile dot plots in his [Tapestry talk](https://www.youtube.com/watch?v=E1kSnWvqCw0) on visualizing uncertainty, which is where I discovered the chart type. Here, each point is percentile. For every 100 DVS members, we'd expect 10 to self-rate as 1.0 or lower for their data skills, indicated by the 10 teal dots from 0 to 1. The ggplot and the code to produce it is at the bottom of this post.\n1. I saved the ggplot as an SVG brought the plots into Figma, a design tool that I hadn't used before. It took a lot of fiddling and even a little math to align the plots with the edges of an equilateral triangle. [Here's a link](https://www.figma.com/file/RwtqrHqLVCRtw4JfEkaN86mr/awein-dvs-thing?node-id=0%3A1) to where the chart was after Figma. (I first tried to do this step with [Google Drawings](https://docs.google.com/drawings/d/1xEtY2v9DeuzBPtwlRMrupECacw00YtqNityAtYFSK60/edit?usp=sharing) before switching to Figma.) I've been wanting to experiment with charts are on tilted axes since getting inspired by *Info We Trust* by RJ Andrews. But while [RJ's connected scatterplot](https://twitter.com/infowetrust/status/1048255077377404928) uses the title to enhance insight, my use actually makes it harder to understand the data!\n1. I then exported the work from Figma as an SVG in order to animate the chart using CSS. This was also a new experience for me and it took a lot of fiddling. My starting point for how to spin the chart was [this Codepen example](https://codepen.io/eveness/pen/BjLaoa). The rotation makes it even harder to draw genuine insight from the chart. Good thing that's not the point.\n1. Finally, I brought the SVG and CSS code back into R with the `htmltools` package, so I could include it in this blog you're currently reading, which was made using [RMarkdown and blogdown](https://bookdown.org/yihui/blogdown/).",[],[210,343],"40defd7e-a9cd-11f0-8de7-0224a6c84d84","dbt-cheatsheet.md","7e7b2e12-2946-11f0-baff-569c3dd06744",94,["D",1760535947009],"## Cheatsheet for a dbt technical assessment\n\nI have a job interview and wanted to have stuff handy.\n\n### Problem solving strategy:\n\nDon't be dogmatic about following this but it's better than no plan.\n\n1. Ask questions to understand stakeholder needs \n - User story: Dashboard will be visited by this persona at this cadence to make this decision.\n\n1. Ask questions to understand data\n\n1. Sketch dashboard (on pen and paper) \n - Chart elements \n - Define filters\n\n1. SQL queries to generate input to dashboard elements using `some_ideal_clean_and_pristine.table_that_you_think_exists`\n\n1. Before starting model, consider doing sensemaking queries on the source data.\n\n1. Start writing the dbt model to produce `some_ideal_clean_and_pristine.table_that_you_think_exists` \n 1. Configure sources, test non-null/uniqueness \n 1. Speak to materialization strategy (don’t add `is_incremental` until you have a running model) \n 1. Create model. Start with import CTEs. \n 1. Scaffold with step-by-step CTEs before you start hacking away. \n 1. Filter near the top: \n - Deleted records \n - Excluded users \n - Events too old to be relevant\n\n1. Create yaml\n - add relevant tests\n\n1. Make the dashboard\n\n\n### Incremental syntax:\n\n#### [Big easy](https://discourse.getdbt.com/t/on-the-limits-of-incrementality/303)\n```\nwith events as (\n select * from {{ref('events')}}\n {% if is_incremental() %}\n where event_timestamp >= (select max(event_timestamp)::date from {{this}})\n {% endif %}\n)\n--rest of model...\n```\n\n#### Unique Key ([Incremental Models docs](https://docs.getdbt.com/docs/build/incremental-models))\n\n```\n{{\n config(\n materialized='incremental',\n unique_key='date_day'\n )\n}}\n\nselect\n date_trunc('day', event_at) as date_day,\n count(distinct user_id) as daily_active_users\n\nfrom {{ ref('app_data_events') }}\n\n{% if is_incremental() %}\n\n -- this filter will only be applied on an incremental run\n -- (uses >= to include records arriving later on the same day as the last run of this model)\n where date_day >= (select coalesce(max(date_day), '1900-01-01') from {{ this }})\n\n{% endif %}\n\ngroup by 1\n```\n\n#### Run full refresh\n\n`dbt run --full-refresh --select my_incremental_model+`\n\n### [Using packages](https://docs.getdbt.com/docs/build/packages#how-do-i-add-a-package-to-my-project)\n\nI probably want to avoid packages, but if you need codegen or utils, create a `packages.yml` file in the dir as the project yaml, and run `dbt deps`. \n```\npackages:\n - package: dbt-labs/codegen\n version: 0.10.0\n\n```\n\nRun `dbt deps`. And then here's the [command line](https://hub.getdbt.com/dbt-labs/codegen/latest/)\n\n`dbt --quiet run-operation generate_source --args '{\"table_names\": [\"orders\"]}' > models/staging/jaffle_shop/_sources.yml`\n\n\n#### Redshift materializations\n\n- Late binding views `WITH NO SCHEMA BINDING;` in dbt it's `{{ config(materialized='view', bind=False) }}`.\n- Auto-refresh\n\n#### Redshift key config in dbt\n\nVia the [dbt docs for Redshift config](https://docs.getdbt.com/reference/resource-configs/redshift-configs#using-sortkey-and-distkey)\n\nBe up-front about not being super familiar. But my basic understanding is: distkey is for joining, sortkey is for grouping and filtering.\n\n```\n-- Example with one sort key\n{{ config(materialized='table', sort='reporting_day', dist='unique_id') }}\n\nselect ...\n\n\n-- Example with multiple sort keys\n{{ config(materialized='table', sort=['category', 'region', 'reporting_day'], dist='received_at') }}\n\nselect ...\n\n\n-- Example with interleaved sort keys\n{{ config(materialized='table',\n sort_type='interleaved'\n sort=['category', 'region', 'reporting_day'],\n dist='unique_id')\n}}\n\nselect ...\n```\n\n#### window syntax\n\nunbounded: Where BETWEEN is not specified, the frame is implicitly bounded by the current row. For example, ROWS 5 PRECEDING is equal to ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Also, ROWS UNBOUNDED FOLLOWING is equal to ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.\n\n- seems like `over (order by day rows unbounded preceding)` is enough\n\n⬜⬜⬜🟨⬜
\n🟨🟨⬜🟨🟨
\n🟨🟩⬜🟩🟩
\n⬜🟩🟩🟩🟩
\n🟩🟩🟩🟩🟩",[],[210,350],["D",1746404319453],[226,227],[210],["D",1741530120955],["D",1789513278143],{"_104":103,"_112":108},{"_104":103,"_105":106,"_107":108,"_18":54,"_109":110,"_111":25},{"_13":89,"_32":90,"_42":41,"_91":43,"_92":93,"_94":95,"_96":49,"_97":98,"_99":100,"_101":44,"_102":103},"ctx",{"_82":-5,"_134":398,"_399":400,"_401":402,"_403":25,"_404":25,"_405":25,"_406":58,"_407":-5,"_408":-7,"_409":-7,"_410":63,"_411":25,"_412":25,"_413":-5,"_414":25,"_415":25,"_416":25,"_417":-5},"ENV",{"_369":370,"_371":372,"_373":90,"_374":375,"_376":377,"_378":379,"_380":381,"_382":383,"_384":385,"_386":387,"_388":389,"_390":391,"_392":393,"_394":395,"_396":397},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v103