Andre D. Wagner’s Eye for the Photo-Ready
Search
1000 results for “D_W”
-
Ye’d wonder foo the seasons rin
This side o’ Tweed an’ Tyne;
The hairst’s awa; October-month
Cam’ in a whilie syne…—Violet Jacob, “The Neep-Fields by the Sea”
published in THE NORTHERN LIGHTS & Other Poems (John Murray, 1927)Digitised by the National Library of Scotland
#Scottish #literature #poem #poetry #Scots #Scotslanguage #20thcentury #womenwriters #autumn
-
#alphabetchallenge #TTRPG W-Wheel of Time-Wizards of the Coast-3rd edition D&D with unique species, prestige classes, and rules for the One Power. #wheeloftime #wheeloftimeseries #wotc #wizardsofthecoast #fantasy #FantasyWorld #epicfantasyseries #epicfantasybooks #EpicFantasyReads #epicfantasynovels #ttrpgpodcast https://www.k-squareproductions.com/gmbc
-
The Quest Begins (The “Why”)
I still remember the first time I opened LeetCode and felt like I’d stepped into the Upside Down from Stranger Things—every problem looked like a monster lurking in the dark, and I had no idea which weapon to grab. I’d spend hours staring at a prompt, scribbling a brute‑force solution, then immediately giving up when I saw the optimal answer tucked away in the discussion tab. It felt like I was running on a treadmill: lots of effort, zero progress.The turning point came after a mock interview with a friend who worked at Google. He didn’t care about my code; he cared about how I thought. When I stumbled, he asked, “Can you walk me through your plan before you write a single line?” I froze. I realized I’d been treating interview prep like a memorization marathon instead of a conversation. I needed a technique that forced me to explain my thinking, not just vomit code.
The Revelation (The Insight)
After a week of trial and error, I landed on one simple, repeatable loop that turned my preparation from a grind into a game:The Pattern‑Practice Loop
- Pick a pattern (e.g., Sliding Window, Two‑Pointers, Backtracking).
- Solve one problem using only that pattern—no peeking at solutions, no “I’ll just brute‑force first.”
- Teach the solution out loud to a rubber duck (or an imaginary interviewer) while walking through every line, explaining why each step works.
That’s it. No fancy frameworks, no endless lists of problems. By forcing myself to articulate the pattern before typing, I built a mental model that survived the pressure of a real interview.
Why does this work? Interviewers aren’t looking for a memorized answer; they want to see you recognize a familiar structure and adapt it. When you can name the pattern, you instantly know which tools to reach for—just like a Jedi knows which lightsaber form to use against a particular opponent.
Wielding the Power (Code & Examples)
Let’s make this concrete with the Sliding Window pattern, a frequent flyer in FAANG interviews.The Struggle (What NOT to do)
# ❌ Bad attempt: brute force O(n^2) without recognizing the window
def max_sum_subarray(arr, k):
max_sum = 0
for i in range(len(arr) - k + 1):
current = 0
for j in range(i, i + k):
current += arr
[j] max_sum = max(max_sum, current)
return max_sumWhat’s wrong?
- No explanation of why we’re nested looping.
- No mention of the sliding window idea; we’re just re‑summing from scratch each time.
- In an interview, you’d stare at this and think, “There’s gotta be a better way,” but you’d waste time figuring it out on the spot.
The Victory (After applying the Pattern‑Practice Loop)
First, I named the pattern: “We need a sliding window of sizekthat moves one step at a time, keeping a running sum.”Then I solved it without looking at any solution:
def max_sum_subarray(arr, k):
# 1️⃣ Initialize the sum of the first window
window_sum = sum(arr[:k])
max_sum = window_sum# 2️⃣ Slide the window: subtract the element leaving, add the one entering
for i in range(k, len(arr)):
window_sum += arr[i] - arr[i - k] # O(1) update
max_sum = max(max_sum, window_sum)return max_sum
Finally, I taught it to my rubber duck:
“We start by summing the first
kelements—that’s our initial window. As we move the window right by one, we drop the leftmost element and add the new rightmost element. Because we update the sum in constant time, the whole algorithm runs in O(n) with O(1) extra space.”
Saying that out loud forced me to verify each line: Why doeswindow_sum += arr[i] - arr[i - k]work? Because we removearr[i‑k](the element that just left) and addarr[i](the new entry). If I couldn’t explain it, I knew I hadn’t truly internalized the pattern.Common Traps to Avoid
TrapWhat it looks likeHow to dodge itPeeking at the solutionOpening the discussion tab before you’ve triedSet a timer (15 min). If you haven’t written anything, step away, then return with fresh eyes.Skipping the teach‑backWriting code, then moving onAfter each solution, spend 2 min explaining it aloud. Record yourself if you’re alone—listening back reveals gaps.Mixing patternsTrying to apply sliding window to a problem that needs two‑pointersBefore coding, ask: “Which pattern fits the constraints?” Write the pattern name as a comment.Why This New Power Matters
By the end of three months, I’d cycled through eight core patterns (sliding window, two‑pointers, binary search, backtracking, BFS/DFS, heap, trie, DP). Each week I treated the pattern like a new move in a fighting game—practicing it in isolation, then chaining it into combos (mixed‑pattern problems).The payoff?
- Interview confidence: I could glance at a problem, whisper the pattern name, and instantly know the approach.
- Reduced anxiety: No more blank‑page panic; I had a mental toolbox.
- Faster coding: With the pattern named, I wrote the correct solution on the first try 70 % of the time.
And the best part? The technique scales. Whether you’re prepping for a frontend role (think “state‑management patterns”) or a backend role (think “consistency patterns”), the same loop works: identify, solve, teach.
Your Turn – The Challenge
Pick one pattern you’ve seen but never truly mastered (e.g., Backtracking).- Grab a timer—20 minutes.
- Solve a single problem using only that pattern (no looking at solutions).
- Spend three minutes explaining your solution out loud to a rubber duck, a pet, or even your houseplant.
When you finish, drop a comment with the pattern you chose and how the teach‑back felt. I’ll read every reply and cheer you on—consider this your own training montage.
Now go crush that interview like Rocky crushing those meat‑slabs in the meat locker—one solid, patterned punch at a time! 🚀#interview #career #programming #softwareengineering #software #coding #development #engineering #inclusive #community
Level Up Your FAANG Interview: A 3‑Month Training Montage Inspired by Rocky -
https://www.europesays.com/at/460199/ Vitamin C oder Vitamin D – was ist bei einer Erkältung besser? #AT #Atemwegsinfektion #Austria #Erkältung #Erkältungszeit #Gesundheit #Health #Österreich #Präparat #TheBMJ #VitaminC #VitaminD #Vitamine #Vorbeugung
-
I will never be rich because I only ever want to do super niche things with an inherently limited audience. Like the only books I wanna write are slasher books (not a usually very literary genre) or if I designed a video game I'd want to exclusively release it for the Atari Jaguar.
-
#WritersCoffeeClub 18/10: Talk about a time you had a revelation.
It was shortly after I’d self-published How to Succeed as a Supervillain by Following the Evil Overlord List when I remembered an earlier novel I’d written, which lay languishing in a metaphorical draw on my laptop, had thematic similarities.
I dug it out, reworked it so it had a similar word count, and lo and behold I had a companion piece entitled The Secret Life of Books.
Currently looking to get it published, hopefully in a traditional manner. If it doesn't get picked up it'll be back to self-publishing.
#WritersOfMastodon #TalesFrom… #SecretLife… #HowtoSucceed… #Author #Novel
-
LAST WEEKEND for #BannedBooksWeek with #Bookshoporg 💪
Thru Oct 18th, 15% off titles challenged in schools & libraries! 👀
https://bookshop.org/lists/it-s-officially-banned-books-week
#AClockworkOrange is one where I'd have been worse off if the book had been banned. I'd watched the movie cold & unprepared, by machinations of an ex-friend.
Getting the book from the library helped me sort out my head after that shock. 😅
#BannedBooks #BookSale #Books #LiteraryBooks #BooksBooksBooks #Bookstagram #Writing #AdultFiction @bookstodon
-
Applied Wizardry
A podcast for engineers and technical founders about how we build with AI: the engineering behind the magic...Great Australian Pods Podcast Directory: https://www.greataustralianpods.com/applied-wizardry/
#AusPods #Podcasts #Podcasting #Australia #Technology #Cyber #AI
-
CW: NSFW
PART 1 Sometimes I get SO horny I just cant think An liftdizznutz is a brand new creator and I couldnt
Video Link: https://luluvido.com/d/h900l95rlvahPART 2 Sometimes I get SO horny I just cant think An liftdizznutz is a brand new creator and I couldnt
Video Link: https://luluvido.com/d/wzsnzey2osth -
STOP THE WORLD, HE WON'T GET OFF
No news b4 1930 PST, nice!
per Yahoo:
Trump waived sanctions he signed last week. "A lot of you don't know our decision but we'll figure it out"
(was for next week)
[aimed at avoiding anything "that will drive, foolishly, Russia away from" (talks)
"I think the president wants... to sort of analyze what imposing both sanctions and tariffs will mean to...the economy " Rubio told reporters before boarding a plane…
Poor man, interpreting incoherence, it’s not “figured out” yet. I could say things b4 boarding a plane too--you’d wait a long time to ask me anything in future.
Ukrainians observing Christmas in the snow, or without heat, breadwinners dead, but Americans are paying too much for their fossil fuels, it's awful!
A really childish mentality & putrid personality, IQ 73, might try to keep the world on a diet of horseshit, try to leave this for the next President; "Putin might be hanging from the highest tree soon" [or who?] -
Weekly output: satellite-to-phone usage map, BEAD buildout blues, anti-scam Android updates, best inventions of 2026, inflight WiFi (x3), unlocked smartphones, Zayo, Blue Origin
Just three days after returning from one transatlantic trip, I’m heading back across the pond–this time, to Milan for the Consumer Technology Association’s CES Unveiled event there. I’m doing one onstage interview for CTA, while the Italian Trade Agency is covering my airfare and lodging.
10/12/2026: Where Are People Using Cellular Satellite Services? This Map Will Show You, PCMag
Ookla offered me an advance look at this map showing where their software has observed direct-to-device connections from satellites (mostly Starlink) to phones, plus an interview with a product-management VP who offered more insight than I would have gotten from any press release.
10/12/2026: A BEAD ‘Twilight Zone.’ ISPs Annoyed by Delays With Broadband Funding Rollout, PCMag
This post provided an excellent reminder that watching a livestream of a conference panel is a poor substitute for attending in person–after watching this discussion at the AnchorNets conference in Crystal City, I interviewed the attendee who had vented about his experience with the federal broadband-buildout program, then talked to one of the panelists later in the day.
10/14/2026: Google’s Latest Android Updates Use AI to Clamp Down on Mobile Scams, PCMag
This post took very little time to write, which was a good thing considering that I had to write it between podcast interviews at an AI conference in Dublin.
10/15/2026: The Best Inventions of 2026, Time
My parents subscribed to Time when I was in high school, so it was neat to get my first byline there–five of them, in the form of profiles for this annual package. In order of their appearance on this long list: Bluetooth Auracast broadcast audio, the Gigs OS prepaid wireless service platform, TransUnion’s Branded Call Display, Lightmatter’s Guide photonics engine for data centers, and Major League Baseball’s Automated Ball-Strike Challenge system.
10/15/2026: Almost Half of United Airlines Departures Now Feature Starlink Connectivity, PCMag
I thought I’d write this post via Starlink on my Aer Lingus flight home from Dublin, but instead jet lag had me awake in the middle of the night. Then I did use that inflight broadband to field a request for a correction–an advance copy of United’s press release had the wrong address for their Starlink tracker, which United’s PR rep promptly brought to our attention.
10/15/2026: Southwest Airlines Updates Starlink Flight Plan, Will Upgrade Entire Fleet, PCMag
I updated the piece I wrote in February with this news about Southwest’s decision to install Space’s broadband across its entire fleet.
10/16/2026: The best way to get great airline WiFi — and why you might not want to, The Washington Post
I wrote this to reject the notion–one you see all the time on X–that Starlink is the only inflight WiFi that matters, and that any airline unwilling to install it deserves to have its planes crushed into beer cans. I thought this piece would draw more comments given how strongly many people feel about Elon Musk, by which I mean how much many people hate Elon Musk, but as you can see it did not.
10/16/2026: Buying a phone from a wireless carrier has limits. There’s another option., The Washington Post
This week’s tech tip was inspired in part by my mother-in-law shopping for a new iPhone; I pointed out that by buying a new model at the local Apple Store, she could get it unlocked, get a trade-in on her old iPhone, and have somebody at the Genius Bar help with transferring over everything from the old phone.
10/16/2026: How Zayo is trying to keep its cool in an overheating data center market, Light Reading
I wrote this the previous week, itself a week after I’d interviewed the CTO of this telecom-infrastructure firm at SCTE TechExpo in Atlanta, and then it took a little more time to get the piece edited and published.
10/16/2026: Blue Origin Unveils Plans for Massive Upgrade to Its Florida Facilities, PCMag
I thought I’d have a low-key Friday, and then Blue Origin announced these plans for a serious expansion of its Cape Canaveral infrastructure–some of which I’d seen in the distance at the end of August.
#AnchorNets #android #BEAD #BlueOrigin #BluetoothAuracast #callerID #CapeCanaveral #dataCenters #directToDevice #EutelsatOneWeb #GigsOS #howToBuyUnlockedPhone #inflightWiFi #Lightmatter #MLB #NewGlenn #Ookla #photonics #satelliteRoaming #scamTexts #SCTETechExpo #Southwest #SpaceCoast #Starlink #StarlinkMobile #Time #TransUnion #UA #UnitedAirlines #unlockedPhones #WN #Zayo -
TLC Driver Education 24 Hour Course Providers
Source: https://data.cityofnewyork.us/d/wzur-rhz9
#nyc #data #dataviz -
Introduction
Up to now in our SQL class, I could useSELECT,WHERE,GROUP BYandORDER BYto get simple answers out of a table. This assignment pushed me further. We had to research four topics and show how each one works on our hospital database:- Part 1: Window functions
- Part 2: Joins
- Part 3: CTEs (Common Table Expressions)
- Part 4: SQL Subqueries
In this article I explain each one the way I came to understand it. For every idea, I'll say what it does, why you would use it, show a query I ran on the hospital data, and explain what the result told me. I've also written down the mistakes I made along the way, because those taught me the most.
I ran every query on my local PostgreSQL 13 on Windows, and the results shown are exactly what I got back.
The hospital database I used
The tables live in a schema (a folder for tables) calledmorning. I ran this first, so I didn't have to typemorning.in front of every table name:SET search_path TO morning;
There are seven tables. The main one is
TableOne row is...Rowsvisits, where each row is one visit to the hospital. The other six tables hold the details about each visit.visitsone visit30patientsone patient25doctorsone doctor4departmentone department4wardsone ward4diagnosisone diagnosis18proceduresone procedure15
The first thing I noticed is thatvisitsdoesn't store any names. It only stores ID numbers likepatient_idanddoctor_id, which point to the other tables. It also stores facts about each visit: the date, the type of visit (Outpatient, Inpatient or Emergency), how many days the patient stayed, the bill in Kenya shillings (total_bill_kes), the satisfaction score, and a few more.Before writing any queries, I looked through the data and wrote down a few facts that helped me read my results:
- There are four doctors, and each one works in one department:
- Dr. Brian Mwangi (101): Cardiology (10)
- Dr. Sarah Wanjiku (102): General Medicine (20)
- Dr. Peter Otieno (103): Orthopedics (30)
- Dr. Faith Chebet (104): Pediatrics (40)
- All the visits happened between 1 and 15 June 2026, two visits a day.
- Five patients came twice: patients 1, 2, 5, 7 and 10.
- All 30 visits together were billed KES 488,000.
Assumptions I made
- I used the data exactly as it is in the script. I didn't add, remove or fix anything. Names are shown as they're saved, so one doctor appears as
DR.Brian Mwangi. - "Week 1" means 1 to 7 June. "Week 2" means 8 to 15 June.
- In this data, every patient, doctor and department has at least one visit. I found out this makes some joins look the same, so in a few places I asked a narrower question (like "Emergency visits only") to show the difference.
- The data has no empty (
NULL) IDs.
Part 1: Window Functions
1.1 The problem window functions solve
I already knewGROUP BYfrom class. It combines many rows into one row per group:SELECT doctor_id, SUM(total_bill_kes) AS doctor_total
FROM visits
WHERE doctor_id IN (103, 104)
GROUP BY doctor_id;doctor_id | doctor_total
-----------+--------------
103 | 105200.00
104 | 74900.00
(2 rows)That gave me the totals, but I lost the individual visits. I wanted to see each visit with the doctor's total beside it, and with
GROUP BYalone I couldn't do that.That's exactly what a window function does. It does the maths for a group, but it doesn't combine the rows. Every row remains.
SELECT visit_id, doctor_id, total_bill_kes,
SUM(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_total
FROM visits
WHERE doctor_id IN (103, 104)
ORDER BY doctor_id, visit_id;visit_id | doctor_id | total_bill_kes | doctor_total
----------+-----------+----------------+--------------
1003 | 103 | 28000.00 | 105200.00
1008 | 103 | 6500.00 | 105200.00
1015 | 103 | 24000.00 | 105200.00
1020 | 103 | 6500.00 | 105200.00
1023 | 103 | 7200.00 | 105200.00
1027 | 103 | 33000.00 | 105200.00
1004 | 104 | 42000.00 | 74900.00
1007 | 104 | 5500.00 | 74900.00
1014 | 104 | 1800.00 | 74900.00
1019 | 104 | 5200.00 | 74900.00
1024 | 104 | 18500.00 | 74900.00
1028 | 104 | 1900.00 | 74900.00
(12 rows)The totals are the same as before, but all 12 visits are still there.
We learn that
GROUP BYgives one row per group. A window function gives every row, plus something about its group.1.2 How a window function is written: OVER, PARTITION BY, ORDER BY and window frames
I learnt that a window function always has the wordOVERafter it. The brackets describe the "window", which is just the set of rows the function looks at.function_name() OVER (
PARTITION BY column -- split rows into groups (optional)
ORDER BY column -- sort rows inside each group (optional)
ROWS BETWEEN ... -- the frame: which nearby rows to use (optional)
)Here's what I learnt about each part.
OVER()
Empty brackets mean "look at all the rows". I used this to put the hospital's grand total next to every visit and work out each visit's share of the money:SELECT visit_id, total_bill_kes,
SUM(total_bill_kes) OVER () AS hospital_total,
ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (), 1) AS percent_of_total
FROM visits
ORDER BY total_bill_kes DESC
LIMIT 5;visit_id | total_bill_kes | hospital_total | percent_of_total
----------+----------------+----------------+------------------
1017 | 82000.00 | 488000.00 | 16.8
1010 | 76000.00 | 488000.00 | 15.6
1004 | 42000.00 | 488000.00 | 8.6
1016 | 36000.00 | 488000.00 | 7.4
1027 | 33000.00 | 488000.00 | 6.8
(5 rows)The output told me that one visit (1017, a heart failure patient) brought in almost 17% of all the money in these two weeks. I wouldn't have spotted that from the raw table.
PARTITION BY
PARTITION BYsplits the rows into groups, and the function starts again for each group. It's likeGROUP BY, except the rows aren't combined. I usedPARTITION BY doctor_idabove to get each doctor's total.ORDER BY (inside OVER)
This sorts the rows before the function does its work. It matters whenever order matters: numbering rows 1, 2, 3, looking at the previous row, or keeping a running total.ORDER BYinsideOVER (...)is not the same as theORDER BYat the end of the query. The one inside changes how the answer is worked out. The one at the end only changes how the rows are shown.Window frames
FrameIn plain words
A frame says which rows near the current row to include. These are the ones I used:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWFrom the very first row up to this one (a running total)ROWS BETWEEN 2 PRECEDING AND CURRENT ROWThis row and the two before it (a 3-row average)ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGEvery row in the group
Something I noticed is that when I wrote a running total withORDER BYbut no frame, the numbers looked wrong. I found out that without a frame, PostgreSQL quietly usesRANGEinstead ofROWS, andRANGEtreats rows with the same value as one block. Our hospital has two visits every day, so this is what happened:SELECT visit_id, visit_date, total_bill_kes,
SUM(total_bill_kes) OVER (ORDER BY visit_date) AS running_default,
SUM(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM visits
ORDER BY visit_date, visit_id
LIMIT 6;visit_id | visit_date | total_bill_kes | running_default | running_rows
----------+------------+----------------+-----------------+--------------
1001 | 2026-06-01 | 3500.00 | 6000.00 | 3500.00
1002 | 2026-06-01 | 2500.00 | 6000.00 | 6000.00
1003 | 2026-06-02 | 28000.00 | 76000.00 | 34000.00
1004 | 2026-06-02 | 42000.00 | 76000.00 | 76000.00
1005 | 2026-06-03 | 4000.00 | 83000.00 | 80000.00
1006 | 2026-06-03 | 3000.00 | 83000.00 | 83000.00
(6 rows)running_defaultadds both visits from the same day together. That's why both 1 June rows say 6,000.running_rowsadds one visit at a time: 3,500, then 6,000, then 34,000, and so on.
In the end I understood that neither one is wrong. The first answers "how much by the end of this day?" The second answers "how much up to this visit?"
1.3 Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE
ROW_NUMBER()
This function numbers the rows 1, 2, 3... Every row gets its own number, even if two rows look the same.It can be used to count things in order, like a patient's 1st visit, 2nd visit, and so on.
In my practice, I numbered each returning patient's visits by date.
SELECT patient_id, visit_id, visit_date,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_date) AS visit_number
FROM visits
WHERE patient_id IN (1, 2, 5, 7, 10)
ORDER BY patient_id, visit_number;patient_id | visit_id | visit_date | visit_number
------------+----------+------------+--------------
1 | 1001 | 2026-06-01 | 1
1 | 1011 | 2026-06-06 | 2
2 | 1002 | 2026-06-01 | 1
2 | 1021 | 2026-06-11 | 2
5 | 1005 | 2026-06-03 | 1
5 | 1025 | 2026-06-13 | 2
7 | 1007 | 2026-06-04 | 1
7 | 1028 | 2026-06-14 | 2
10 | 1010 | 2026-06-05 | 1
10 | 1030 | 2026-06-15 | 2
(10 rows)The output showed that because of
PARTITION BY patient_id, the count starts again at 1 for every patient. Any row marked 2 is a return visit.RANK() and DENSE_RANK()
These functions give each row a position, like runners in a race. If two rows tie, they get the same position. The difference is what comes next:RANK()skips a number after a tie: 1, 1, 3DENSE_RANK()doesn't skip: 1, 1, 2
In my practice, I ranked the visits by patient satisfaction score, highest first. Some visits have the same score, so I put all three functions side by side to see how they differ.
SELECT visit_id, patient_satisfaction_score AS score,
ROW_NUMBER() OVER (ORDER BY patient_satisfaction_score DESC, visit_id) AS row_num,
RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS dense_rank
FROM visits
ORDER BY score DESC, visit_id
LIMIT 9;visit_id | score | row_num | rank | dense_rank
----------+-------+---------+------+------------
1011 | 4.8 | 1 | 1 | 1
1028 | 4.8 | 2 | 1 | 1
1005 | 4.7 | 3 | 3 | 2
1019 | 4.7 | 4 | 3 | 2
1007 | 4.6 | 5 | 5 | 3
1015 | 4.6 | 6 | 5 | 3
1025 | 4.6 | 7 | 5 | 3
1001 | 4.5 | 8 | 8 | 4
1013 | 4.5 | 9 | 8 | 4
(9 rows)In the output, visits 1011 and 1028 both scored 4.8, so
RANKandDENSE_RANKboth put them in 1st place.- The next score (4.7) is 3rd with
RANK, because two visits already took 1st and 2nd. WithDENSE_RANKit's 2nd. ROW_NUMBERignores ties completely and just counts 1 to 9.
When I first ran this, tied visits came out of
FunctionDo ties share a number?Does it skip after a tie?I'd use it when I want...ROW_NUMBERin a different order each time. I learnt that if rows tie, PostgreSQL can number them in any order. Addingvisit_idto its sort made the result the same every time.ROW_NUMBER()No–exactly one row per numberRANK()YesYesrace-style positionsDENSE_RANK()YesNothe "top 3 scores", even if more than 3 rows have themNTILE(n)
NTILE(n)splits the rows intongroups of about the same size and says which group each row is in.It is used to sort things into bands, like cheap, middle and expensive.
In practice, I split the 9 inpatient visits into 3 cost groups.
SELECT visit_id, total_bill_kes,
NTILE(3) OVER (ORDER BY total_bill_kes) AS cost_group
FROM visits
WHERE admission_type = 'Inpatient'
ORDER BY total_bill_kes;visit_id | total_bill_kes | cost_group
----------+----------------+------------
1024 | 18500.00 | 1
1015 | 24000.00 | 1
1003 | 28000.00 | 1
1026 | 29000.00 | 2
1027 | 33000.00 | 2
1016 | 36000.00 | 2
1004 | 42000.00 | 3
1010 | 76000.00 | 3
1017 | 82000.00 | 3
(9 rows)As per the output, group 1 holds the three cheapest stays and group 3 the three most expensive. I also read that if the rows don't split evenly, the first groups just get one extra row each.
1.4 Looking at other rows: LAG, LEAD, FIRST_VALUE and LAST_VALUE
LAG() and LEAD()
LAGlooks back at the previous row.LEADlooks ahead at the next row.They are used to compare a row with the one before or after it. For example, "How many days since this patient was last here?"
In my practice, for each returning patient, I showed their previous visit, their next visit, and the number of days in between.
SELECT patient_id, visit_id, visit_date,
LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_visit,
LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit,
visit_date - LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last
FROM visits
WHERE patient_id IN (1, 2, 5, 7, 10)
ORDER BY patient_id, visit_date;patient_id | visit_id | visit_date | previous_visit | next_visit | days_since_last
------------+----------+------------+----------------+------------+-----------------
1 | 1001 | 2026-06-01 | NULL | 2026-06-06 | NULL
1 | 1011 | 2026-06-06 | 2026-06-01 | NULL | 5
2 | 1002 | 2026-06-01 | NULL | 2026-06-11 | NULL
2 | 1021 | 2026-06-11 | 2026-06-01 | NULL | 10
5 | 1005 | 2026-06-03 | NULL | 2026-06-13 | NULL
5 | 1025 | 2026-06-13 | 2026-06-03 | NULL | 10
7 | 1007 | 2026-06-04 | NULL | 2026-06-14 | NULL
7 | 1028 | 2026-06-14 | 2026-06-04 | NULL | 10
10 | 1010 | 2026-06-05 | NULL | 2026-06-15 | NULL
10 | 1030 | 2026-06-15 | 2026-06-05 | NULL | 10
(10 rows)The output showed that patient 1 came back after 5 days, and everyone else came back after 10. A first visit has nothing before it, so
LAGshowsNULL. A last visit has nothing after it, soLEADshowsNULL.FIRST_VALUE() and LAST_VALUE()
FIRST_VALUEtakes the value from the first row in the window.LAST_VALUEtakes it from the last row.Using our hospital data, for each of Dr. Faith Chebet's visits (doctor 104), I showed the bill from her first visit and from her last visit.
SELECT visit_id, visit_date, total_bill_kes,
FIRST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS first_bill,
LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS last_bill_default,
LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_bill_full
FROM visits
WHERE doctor_id = 104
ORDER BY visit_date, visit_id;visit_id | visit_date | total_bill_kes | first_bill | last_bill_default | last_bill_full
----------+------------+----------------+------------+-------------------+----------------
1004 | 2026-06-02 | 42000.00 | 42000.00 | 42000.00 | 1900.00
1007 | 2026-06-04 | 5500.00 | 42000.00 | 5500.00 | 1900.00
1014 | 2026-06-07 | 1800.00 | 42000.00 | 1800.00 | 1900.00
1019 | 2026-06-10 | 5200.00 | 42000.00 | 5200.00 | 1900.00
1024 | 2026-06-12 | 18500.00 | 42000.00 | 18500.00 | 1900.00
1028 | 2026-06-14 | 1900.00 | 42000.00 | 1900.00 | 1900.00
(6 rows)- The output showed that
first_billis 42,000 on every row. That's her first visit (1004), so that part worked straight away. last_bill_defaultjust copied each row's own bill. The reason is the same hidden default frame from earlier. It stops at the current row, so from each row's point of view, the "last" row is itself.last_bill_fulltells it to look at all the rows, and then it correctly shows 1,900, her last visit (1028).
Lesson learnt: whenever I use
LAST_VALUE, I addROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.1.5 Aggregate window functions: SUM, AVG, COUNT, MIN and MAX
I knew these five from theGROUP BYchapter of our notes. What I learnt is that puttingOVER (...)after them turns them into window functions with the same answers, but every row stays.I put each Orthopedics visit (department 30) next to its department's count, total, average, lowest and highest bill.
SELECT visit_id, total_bill_kes,
COUNT(*) OVER (PARTITION BY department_id) AS dept_visits,
SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total,
ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg,
MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min,
MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max
FROM visits
WHERE department_id = 30
ORDER BY visit_id;visit_id | total_bill_kes | dept_visits | dept_total | dept_avg | dept_min | dept_max
----------+----------------+-------------+------------+----------+----------+----------
1003 | 28000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1008 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1015 | 24000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1020 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1023 | 7200.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1027 | 33000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
(6 rows)The results show the average Orthopedics bill is KES 17,533.33. With that number on every row, we see that visits 1003, 1015 and 1027 are above average and the other three are below.
A moving average
AddingORDER BYand a frame turnsAVGinto a moving average, which is an average that slides along the rows. This one averages each visit with the two before it:SELECT visit_id, visit_date, total_bill_kes,
ROUND(AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM visits
WHERE doctor_id = 104
ORDER BY visit_date, visit_id;visit_id | visit_date | total_bill_kes | moving_avg_3
----------+------------+----------------+--------------
1004 | 2026-06-02 | 42000.00 | 42000.00
1007 | 2026-06-04 | 5500.00 | 23750.00
1014 | 2026-06-07 | 1800.00 | 16433.33
1019 | 2026-06-10 | 5200.00 | 4166.67
1024 | 2026-06-12 | 18500.00 | 8500.00
1028 | 2026-06-14 | 1900.00 | 8533.33
(6 rows)The output shows that the first row has nothing before it, so its average is just itself. The second row averages two visits: (42,000 + 5,500) ÷ 2 = 23,750. From the third row on, it always uses three. I could see how a moving average evens out a one-off big bill like that 42,000.
1.6 A mistake I made: putting a window function in WHERE
I wanted the most expensive visit in each department, so my first try was this:SELECT visit_id, department_id, total_bill_kes,
RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank
FROM visits
WHERE RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) = 1;PostgreSQL gave me an error:
ERROR: window functions are not allowed in WHERE
Why it failed: this is where the "order of execution" chapter in our notes finally clicked for me. PostgreSQL runs a query in this order:
FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT. Window functions are worked out in theSELECTstep, which comes afterWHERE. So whenWHEREruns, the rank doesn't exist yet.How I fixed it: the trick is to do it in two steps. First I worked out the rank inside a CTE (I explain CTEs in Part 3), and then I filtered on that rank in the main query. By the time the main query's
WHEREruns, the rank already exists, so it works:WITH ranked AS (
SELECT visit_id, department_id, total_bill_kes,
RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank
FROM visits
)
SELECT visit_id, department_id, total_bill_kes
FROM ranked
WHERE bill_rank = 1
ORDER BY department_id;visit_id | department_id | total_bill_kes
----------+---------------+----------------
1017 | 10 | 82000.00
1016 | 20 | 36000.00
1027 | 30 | 33000.00
1004 | 40 | 42000.00
(4 rows)Now I get the most expensive visit in each of the four departments, which is what I wanted in the first place.
Part 2: Joins
As I mentioned earlier, thevisitstable only has numbers likepatient_id = 3anddoctor_id = 103. The names live in thepatientsanddoctorstables. I learnt that storing each thing only once is good, but it means that to make a report people can read, I have to stitch the tables back together. That's what a join does.Every join needs an
ONpart that says how the rows match up. Usually it's "this ID equals that ID".2.1 INNER JOIN
INNER JOINkeeps only the rows that have matching values in both tables. We use it when we only care about rows that exist on both sides.I showed each visit with the patient's name and the doctor's name.
SELECT v.visit_id, p.patient_name, d.doctor_name, v.total_bill_kes
FROM visits AS v
INNER JOIN patients AS p ON v.patient_id = p.patient_id
INNER JOIN doctors AS d ON v.doctor_id = d.doctor_id
ORDER BY v.visit_id
LIMIT 5;visit_id | patient_name | doctor_name | total_bill_kes
----------+----------------+-------------------+----------------
1001 | Amina Otieno | DR.Brian Mwangi | 3500.00
1002 | John Kariuki | Dr. Sarah Wanjiku | 2500.00
1003 | Grace Naliaka | Dr. Peter Otieno | 28000.00
1004 | David Mutua | Dr. Faith Chebet | 42000.00
1005 | Esther Achieng | DR.Brian Mwangi | 4000.00
(5 rows)v,panddare nicknames for the tables, so I don't have to type the full names every time.v.patient_idjust means "thepatient_idcolumn fromvisits". I also learnt you can join as many tables as you need, oneJOINat a time.Joins work with
GROUP BYtoo. Here's how much each department billed, using the real department names:SELECT dep.department_name, COUNT(*) AS visits, SUM(v.total_bill_kes) AS total_billed
FROM visits AS v
INNER JOIN department AS dep ON v.department_id = dep.department_id
GROUP BY dep.department_name
ORDER BY total_billed DESC;department_name | visits | total_billed
------------------+--------+--------------
Cardiology | 9 | 210100.00
Orthopedics | 6 | 105200.00
General Medicine | 9 | 97800.00
Pediatrics | 6 | 74900.00
(4 rows)The output shows that Cardiology and General Medicine each saw 9 visits, but Cardiology made more than twice as much money.
2.2 LEFT JOIN
TheLEFT JOINkeeps every row from the left table (the one afterFROM), and adds matching rows from the right table. Where there's no match, the right side isNULL(empty).We use it when we want to keep everything from one table, even rows with no match. For example, "all doctors, even the ones who had no emergency cases".
At first I tried a plain
LEFT JOINfrom doctors to visits, and I got the same 30 rows as anINNER JOIN. That confused me until I realised every doctor in our data has visits, so there was nothing unmatched to show. The difference only appears when something has no match. So I decided to ask: which doctors handled an Emergency visit?SELECT d.doctor_name, v.visit_id, v.admission_type
FROM doctors AS d
LEFT JOIN visits AS v
ON d.doctor_id = v.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)The result shows all four doctors are listed, but only Dr. Brian Mwangi had an emergency. The
NULLs for the other three just mean "no emergency visit found".A mistake I learnt from: filtering a LEFT JOIN in WHERE
Before I got the query above right, I had put theEmergencycheck inWHEREinstead of inON, like this:SELECT d.doctor_name, v.visit_id, v.admission_type
FROM doctors AS d
LEFT JOIN visits AS v ON d.doctor_id = v.doctor_id
WHERE v.admission_type = 'Emergency'
ORDER BY d.doctor_id;doctor_name | visit_id | admission_type
-----------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
(1 row)Three of the doctors disappeared. It took me a while to see why, and again the answer was the order SQL runs in.
WHEREruns after the join, so it looks at every row and throws out anything that isn't 'Emergency'. That includes the empty rows theLEFT JOINwas keeping for the other three doctors. So without meaning to, I had turned myLEFT JOINinto anINNER JOIN.What I took away from this: with a
LEFT JOIN, if I'm filtering the right-hand table and still want to keep the rows with no match, the condition goes inON, notWHERE.2.3 RIGHT JOIN
TheRIGHT JOINmirrors theLEFT JOIN. It keeps every row from the right table (the one afterJOIN), and adds matching rows from the left table. It is used when we want to keep every row from one table, even rows with no match. The only difference is which side that table sits on.To really see this, I asked the exact same question as in my
LEFT JOINexample: which doctors handled an Emergency visit?This time I started
FROM visitsand putdoctorson the right, afterRIGHT JOIN. Becausedoctorsis now the right-hand table,RIGHT JOINkeeps every doctor:SELECT d.doctor_name, v.visit_id, v.admission_type
FROM visits AS v
RIGHT JOIN doctors AS d
ON v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)The output is exactly the same as the
LEFT JOIN.A LEFT JOIN Bis the same asB RIGHT JOIN A. From my reading, most people stick toLEFT JOINbecause "start with this table, then add that one" is easier to follow.RIGHT JOINis useful when adding one more table to the end of a long query and you want to keep all of its rows.2.4 FULL OUTER JOIN
FULL OUTER JOINkeeps every row from both tables. Rows that match are joined. Rows that don't match getNULLon the side that's missing.We use it to compare two lists and see what's in one, what's in the other, and what's in both.
I compared the patients who came in Week 1 (1–7 June) with those who came in Week 2 (8–15 June). Each list is a small query in brackets, called a subquery, which I explain in Part 4.
SELECT w1.patient_id AS week1_patient,
w2.patient_id AS week2_patient
FROM (SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-01' AND '2026-06-07') AS w1
FULL OUTER JOIN
(SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-08' AND '2026-06-15') AS w2
ON w1.patient_id = w2.patient_id
ORDER BY COALESCE(w1.patient_id, w2.patient_id);week1_patient | week2_patient
---------------+---------------
1 | NULL
2 | 2
3 | NULL
4 | NULL
5 | 5
6 | NULL
7 | 7
8 | NULL
9 | NULL
10 | 10
11 | NULL
12 | NULL
13 | NULL
NULL | 14
NULL | 15
NULL | 16
NULL | 17
NULL | 18
NULL | 19
NULL | 20
NULL | 21
NULL | 22
NULL | 23
NULL | 24
NULL | 25
(25 rows)From the output:
- 4 patients came in both weeks (2, 5, 7 and 10), so both columns are filled.
- 9 patients came only in Week 1. Patient 1 came twice, but both visits were in Week 1.
- 12 patients came only in Week 2.
An
INNER JOINwould only show the 4 who came both weeks. ALEFT JOINwould only show the 13 from Week 1. TheFULL OUTER JOINshows all 25.(
COALESCE(a, b)means "usea, but if it's empty, useb". I used it from Chapter 19 of our notes to keep the list sorted by patient number.)2.5 CROSS JOIN
CROSS JOINpairs every row from one table with every row from the other. There's noON. 4 rows × 4 rows = 16 rows.We use it when we want every possible pair, like a blank duty rota listing every doctor against every ward.
SELECT d.doctor_name, w.ward_name
FROM doctors AS d
CROSS JOIN wards AS w
ORDER BY d.doctor_id, w.ward_id
LIMIT 6;doctor_name | ward_name
-------------------+-------------------
DR.Brian Mwangi | Cardiac Care Unit
DR.Brian Mwangi | General Ward
DR.Brian Mwangi | Surgical Ward
DR.Brian Mwangi | Childrens Ward
Dr. Sarah Wanjiku | Cardiac Care Unit
Dr. Sarah Wanjiku | General Ward
(6 rows)Without the
LIMITthere are 16 rows: 4 doctors × 4 wards.2.6 SELF JOIN
ASELF JOINjoins a table to itself. I was surprised there's no special keyword for it. You just use the same table twice with two different nicknames. It's used to compare rows in a table with other rows in the same table.We try to answer the question: which patients come from the same county?
SELECT p1.patient_name AS patient_1,
p2.patient_name AS patient_2,
p1.county
FROM patients AS p1
INNER JOIN patients AS p2
ON p1.county = p2.county
AND p1.patient_id < p2.patient_id
ORDER BY p1.county;patient_1 | patient_2 | county
----------------+--------------+---------
Esther Achieng | Brian Omondi | Kisumu
Amina Otieno | Isaac Kamau | Nairobi
(2 rows)The
p1.patient_id < p2.patient_idpart took me a moment to understand. Without it, every patient gets matched with themselves, and every pair shows up twice (Esther–Brian and Brian–Esther).I also tried to match each returning patient's first visit with their later one.
SELECT a.patient_id, a.visit_id AS first_visit, a.visit_date AS first_date,
b.visit_id AS later_visit, b.visit_date AS later_date
FROM visits AS a
INNER JOIN visits AS b
ON a.patient_id = b.patient_id
AND a.visit_date < b.visit_date
ORDER BY a.patient_id;patient_id | first_visit | first_date | later_visit | later_date
------------+-------------+------------+-------------+------------
1 | 1001 | 2026-06-01 | 1011 | 2026-06-06
2 | 1002 | 2026-06-01 | 1021 | 2026-06-11
5 | 1005 | 2026-06-03 | 1025 | 2026-06-13
7 | 1007 | 2026-06-04 | 1028 | 2026-06-14
10 | 1010 | 2026-06-05 | 1030 | 2026-06-15
(5 rows)From my research, self joins are also how you'd show "who is whose boss" if a table had a column like
supervisor_id. Ourdoctorstable doesn't have one, so I couldn't show that here.2.7 How the joins compare
JoinRows kept from the left tableRows kept from the right tableWhen I'd use itMy example
After trying all six, it helped me to put them next to each other. The main difference between them is simply which rows they keep when there's no match:INNER JOINonly matchesonly matchesI only want rows that exist on both sidesVisits with patient and doctor namesLEFT JOINallonly matchesI want everything from the first table, matched or notAll doctors, with any emergency visitsRIGHT JOINonly matchesallSame as LEFT, but the table I want to keep is on the rightSame question, tables swappedFULL OUTER JOINallallI want to compare two lists and see what's missing on either sideWeek 1 patients vs Week 2 patientsCROSS JOINevery pairevery pairI want every possible combinationEvery doctor with every wardSELF JOINthe table joined to itself–I want to compare rows within one tablePatients from the same countyPart 3: CTEs (Common Table Expressions)
Our notes (Chapter 14) introduced CTEs briefly. A CTE lets you give a name to a query and use that name like a table inside a bigger query. It starts with the wordWITH, and it only lasts while that one query runs.WITH name_of_cte AS (
SELECT ...
)
SELECT ... FROM name_of_cte;Why it helps: my queries were getting long and hard to follow. A CTE breaks one big query into small, named steps that read from top to bottom.
3.1 A basic CTE
A basic CTE using the question: which departments billed more than KES 100,000?WITH department_totals AS (
SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id
)
SELECT department_id, total_billed
FROM department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)The result shows that Cardiology (10) and Orthopedics (30) each billed more than KES 100,000.
- The CTE
department_totalsadds up the bills for each department. That gives four rows. - The main query reads from
department_totalslike any other table and keeps only the totals over 100,000.
3.2 Multiple CTEs
I learnt you can write several CTEs in oneWITH, with commas between them, and a later CTE can use an earlier one. For example: which doctors billed more than the average doctor?WITH doctor_totals AS (
SELECT doctor_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY doctor_id
),
average_total AS (
SELECT AVG(total_billed) AS avg_billed
FROM doctor_totals
)
SELECT dt.doctor_id, dt.total_billed, ROUND(a.avg_billed, 2) AS avg_billed
FROM doctor_totals AS dt
CROSS JOIN average_total AS a
WHERE dt.total_billed > a.avg_billed;doctor_id | total_billed | avg_billed
-----------+--------------+------------
101 | 210100.00 | 122000.00
(1 row)- We see that
doctor_totalsworks out each doctor's total, giving 4 rows. average_totaluses those 4 totals to get the average, KES 122,000. That's just 1 row.- The main query puts them together. A
CROSS JOINworks here becauseaverage_totalhas only one row, so it simply adds the average to every doctor's row. Then it keeps only the doctors above it.
The output shows that only Dr. Brian Mwangi (101) billed more than the average doctor.
3.3 CTEs with joins and aggregations
We use the example: who are the top five patients by money spent, and where are they from?WITH patient_spend AS (
SELECT patient_id,
COUNT(*) AS visits,
SUM(total_bill_kes) AS total_spent
FROM visits
GROUP BY patient_id
)
SELECT p.patient_name, p.county, ps.visits, ps.total_spent
FROM patient_spend AS ps
INNER JOIN patients AS p ON ps.patient_id = p.patient_id
ORDER BY ps.total_spent DESC
LIMIT 5;patient_name | county | visits | total_spent
--------------------+-------------+--------+-------------
Peter Ouma | Siaya | 2 | 98000.00
Hellen Jepchirchir | Bomet | 1 | 82000.00
David Mutua | Machakos | 1 | 42000.00
Francis Njuguna | Laikipia | 1 | 36000.00
Ruth Nekesa | Trans Nzoia | 1 | 33000.00
(5 rows)We see that the CTE does the adding up (the aggregation), and the main query adds the names (the join). Peter Ouma spent the most, KES 98,000 over two visits: a heart failure stay, and later an emergency visit for chest pain.
3.4 Recursive CTEs
A recursive CTE is one that uses itself. It builds a list one step at a time, a bit like counting on your fingers. It has two parts, joined byUNION ALL: a starting point, and a next step that takes the last row and makes the next one. It keeps going until a condition tells it to stop.I started with the simplest example I could think of, counting from 1 to 5:
WITH RECURSIVE counter AS (
SELECT 1 AS n -- start at 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5 -- add 1 until we reach 5
)
SELECT n FROM counter;n
---
1
2
3
4
5
(5 rows)Using the hospital data, I built a calendar for Week 1 and counted the visits on each day. The reason for a calendar is that a normal
GROUP BY visit_datecan only show days that had visits. A calendar lists every day, so a quiet day would show 0 instead of just being missing.WITH RECURSIVE calendar AS (
SELECT DATE '2026-06-01' AS day
UNION ALL
SELECT (day + 1)::DATE FROM calendar WHERE day < DATE '2026-06-07'
)
SELECT c.day,
COUNT(v.visit_id) AS visits,
COALESCE(SUM(v.total_bill_kes), 0) AS billed
FROM calendar AS c
LEFT JOIN visits AS v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;day | visits | billed
------------+--------+----------
2026-06-01 | 2 | 6000.00
2026-06-02 | 2 | 70000.00
2026-06-03 | 2 | 7000.00
2026-06-04 | 2 | 12000.00
2026-06-05 | 2 | 91000.00
2026-06-06 | 2 | 6500.00
2026-06-07 | 2 | 8800.00
(7 rows)The output shows that the hospital had exactly 2 visits every day, and 5 June brought in the most money (KES 91,000), mostly from one heart failure patient. I was pleased that this one query uses three things I learnt in this assignment: a recursive CTE, a
LEFT JOINand a total.From my research, recursive CTEs are also the usual way to follow a chain, like "this doctor reports to that doctor, who reports to..." Our data doesn't have that kind of column, so I used a calendar instead.
3.5 CTEs vs subqueries
A subquery (Part 4) is a query written inside another query. While I was learning about CTEs, I kept wondering how they were different from subqueries. So I took my first CTE from 3.1 and rewrote it as a subquery to compare them side by side:SELECT department_id, total_billed
FROM (SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id) AS department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)It gave exactly the same answer. The difference isn't in the result. It's in how the query is written and how easy it is to read. With the CTE, I name the step first and then use it, so it reads from top to bottom. With the subquery, the step is tucked inside the main query, so I have to read from the inside out.
This is how I'd sum up the differences I found:
CTESubqueryWhere you write itAt the top, before the main queryInside the main queryDoes it have a name?YesOnly when it's inFROMCan you use it twice in the same query?Yes, just use the name againNo, you'd have to type it out againCan it repeat itself (recursive)?YesNoEasy to read with many steps?Yes, top to bottomHarder, with brackets inside bracketsBest forStep-by-step logic and reusing resultsShort, one-off checks
I also read that in PostgreSQL 12 and newer, a simple CTE and the same subquery run about as fast as each other. So my takeaway is to pick whichever one is easier to read.
Part 4: SQL Subqueries
A subquery is aSELECTinside another query, wrapped in brackets. PostgreSQL answers the small question first, then uses that answer in the big one. You can put a subquery inWHERE,SELECTorFROM.4.1 Scalar (single-value) subqueries
This is a subquery that gives back just one value: one row, one column. "Scalar" turned out to be just the fancy name for that. It can be used to compare against a number I'd otherwise have to look up first, like an average.Scalar subquery in WHERE
For example, to answer the question: which visits cost more than the average visit?First I checked the average on its own:
SELECT AVG(total_bill_kes) FROM visits;
avg
--------------------
16266.666666666667
(1 row)Instead of copying that number into another query by hand, I let a subquery work it out:
SELECT visit_id, patient_id, total_bill_kes
FROM visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits)
ORDER BY total_bill_kes DESC;visit_id | patient_id | total_bill_kes
----------+------------+----------------
1017 | 16 | 82000.00
1010 | 10 | 76000.00
1004 | 4 | 42000.00
1016 | 15 | 36000.00
1027 | 24 | 33000.00
1026 | 23 | 29000.00
1003 | 3 | 28000.00
1015 | 14 | 24000.00
1030 | 10 | 22000.00
1024 | 22 | 18500.00
(10 rows)The output shows that 10 of the 30 visits cost more than the average (KES 16,266.67). I also realised that if new visits are added, the query still works, because it works out the average fresh every time.
Scalar subquery in SELECT
To answer the question: what share of the hospital's money did each department bring in?SELECT department_id,
SUM(total_bill_kes) AS department_total,
ROUND(100.0 * SUM(total_bill_kes) / (SELECT SUM(total_bill_kes) FROM visits), 1) AS percent_of_hospital
FROM visits
GROUP BY department_id
ORDER BY department_id;department_id | department_total | percent_of_hospital
---------------+------------------+---------------------
10 | 210100.00 | 43.1
20 | 97800.00 | 20.0
30 | 105200.00 | 21.6
40 | 74900.00 | 15.3
(4 rows)The output shows that Cardiology (10) brought in 43% of all the money. The subquery gives the hospital total (488,000), and each department's total is divided by it.
4.2 Non-correlated vs correlated subqueries
This was the difference that took me longest to get:- A non-correlated subquery stands alone. You could copy it out and run it by itself, and PostgreSQL only runs it once. Both examples above are like this.
- A correlated subquery borrows a value from the outer query, so it can't run by itself. It's worked out again for each row.
For example (correlated), to answer the question: which visits cost more than the average for their own department? I thought it was fairer to compare a Cardiology visit with other Cardiology visits, not with the whole hospital.
SELECT v.visit_id, v.department_id, v.total_bill_kes
FROM visits AS v
WHERE v.total_bill_kes > (SELECT AVG(v2.total_bill_kes)
FROM visits AS v2
WHERE v2.department_id = v.department_id)
ORDER BY v.department_id, v.total_bill_kes DESC;visit_id | department_id | total_bill_kes
----------+---------------+----------------
1017 | 10 | 82000.00
1010 | 10 | 76000.00
1016 | 20 | 36000.00
1026 | 20 | 29000.00
1009 | 20 | 15000.00
1027 | 30 | 33000.00
1003 | 30 | 28000.00
1015 | 30 | 24000.00
1004 | 40 | 42000.00
1024 | 40 | 18500.00
(10 rows)For reference, the department averages are: Cardiology 23,344.44, General Medicine 10,866.67, Orthopedics 17,533.33 and Pediatrics 12,483.33.
The output shows that visit 1009 (General Medicine, KES 15,000) is below the hospital average, so my earlier query skipped it. But it's above the General Medicine average, so this query includes it. The part that makes it correlated is
Non-correlatedCorrelatedBorrows from the outer query?NoYesCan you run it by itself?YesNoHow often does it run?OnceOnce per outer rowMy exampleAbove the hospital averageAbove their department's averageWHERE v2.department_id = v.department_id: the inside query borrowsvfrom the outside query.4.3 Nested subqueries
A nested subquery is a subquery inside another subquery. PostgreSQL starts with the innermost one and works its way out.My example: which patients saw the doctor who billed the most?
SELECT patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE doctor_id = (
SELECT doctor_id
FROM visits
GROUP BY doctor_id
ORDER BY SUM(total_bill_kes) DESC
LIMIT 1))
ORDER BY patient_name;patient_name
--------------------
Amina Otieno
Catherine Musyoka
Esther Achieng
Hellen Jepchirchir
John Kariuki
Peter Ouma
(6 rows)How I read it, from the inside out:
- The innermost query finds the doctor who billed the most: 101, Dr. Brian Mwangi (KES 210,100).
- The middle query finds the patients he saw: 1, 2, 5, 10, 12 and 16.
- The outer query swaps those numbers for names.
4.4 Subqueries with IN
IN (subquery)keeps a row if its value is on the list the subquery gives back.My example: which visits were for a chronic (long-term) disease?
SELECT visit_id, patient_id, diagnosis_id
FROM visits
WHERE diagnosis_id IN (SELECT diagnosis_id
FROM diagnosis
WHERE diagnosis_category = 'Chronic Disease')
ORDER BY visit_id;visit_id | patient_id | diagnosis_id
----------+------------+--------------
1001 | 1 | 501
1005 | 5 | 505
1010 | 10 | 510
1011 | 1 | 505
1013 | 12 | 501
1017 | 16 | 510
1021 | 2 | 501
1025 | 5 | 501
(8 rows)Findings: the subquery gives back three chronic diseases (501 Hypertension, 505 Diabetes and 510 Heart Failure), and 8 visits were for one of them.
A trap I read about: NOT IN with an empty value
NOT INkeeps rows that are not on the list. But I learnt that if the list has even oneNULLin it,NOT INgives back nothing at all. Our data has noNULLIDs, so I tested it with a tiny list typed by hand:SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2);
patients_found
----------------
23
(1 row)SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2, NULL);
patients_found
----------------
0
(1 row)Our notes taught us that
NULLmeans "we don't know". PostgreSQL can't be sure patient 3 isn't the unknown value, so it plays it safe and keeps nothing. If a list might contain aNULL,NOT EXISTS(next) is the safer choice.4.5 Subqueries with EXISTS
EXISTSasks a yes-or-no question: "Is there at least one matching row?" It doesn't care what's in the row, only whether there is one. That's why people usually writeSELECT 1inside it.For example, which doctors had at least one emergency visit, and which had none?
SELECT d.doctor_name
FROM doctors AS d
WHERE EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');doctor_name
-----------------
DR.Brian Mwangi
(1 row)SELECT d.doctor_name
FROM doctors AS d
WHERE NOT EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');doctor_name
-------------------
Dr. Sarah Wanjiku
Dr. Peter Otieno
Dr. Faith Chebet
(3 rows)The output gives the same answer as my
LEFT JOINexample in Part 2, just asked as a yes-or-no question for each doctor. I also noticed the inside query borrowsd.doctor_idfrom the outside, soEXISTSsubqueries are nearly always correlated.INEXISTSThe question it asks"Is this value on the list?""Is there at least one matching row?"What the subquery gives backA list of valuesRows (only whether there are any matters)Safe withNULLwhen you use NOT?No,NOT INbreaksYes,NOT EXISTSis safe4.6 Subqueries with ANY and ALL
ANYandALLcompare one value with a whole list, using>,<,=and so on. This is how I made sense of them:> ANY (list)means "bigger than at least one of them", so bigger than the smallest.> ALL (list)means "bigger than every one of them", so bigger than the biggest.
My example: I compared Cardiology visits (department 10) with Orthopedics bills (department 30). The six Orthopedics bills are 6,500, 6,500, 7,200, 24,000, 28,000 and 33,000.
ANY: which Cardiology visits cost more than at least one Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ANY (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;visit_id | total_bill_kes
----------+----------------
1013 | 7000.00
1025 | 7600.00
1030 | 22000.00
1010 | 76000.00
1017 | 82000.00
(5 rows)ALL: which Cardiology visits cost more than every Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ALL (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;visit_id | total_bill_kes
----------+----------------
1010 | 76000.00
1017 | 82000.00
(2 rows)> ANYkept the 5 Cardiology visits over 6,500, the cheapest Orthopedics bill.> ALLkept only the 2 over 33,000, the most expensive Orthopedics bill.- I also found out that
= ANY (...)means exactly the same asIN (...).
4.7 Subqueries in FROM
A subquery inFROMacts like a temporary table. I learnt that it must have a nickname. Here I called ittotals.For example, what's the average amount billed per doctor? That takes two steps: first each doctor's total, then the average of those totals.
SELECT ROUND(AVG(doctor_total), 2) AS avg_per_doctor
FROM (SELECT doctor_id, SUM(total_bill_kes) AS doctor_total
FROM visits
GROUP BY doctor_id) AS totals;avg_per_doctor
----------------
122000.00
(1 row)The output shows that the four doctor totals are 210,100, 97,800, 105,200 and 74,900, and their average is KES 122,000. That's the same number I got with two CTEs in Part 3, which was a nice way to check my work. You can't write
AVG(SUM(...))in one go, so the subquery handles the first step.4.8 Where a subquery can go: SELECT, FROM and WHERE
WhereWhat it needs to give backMy exampleWHEREOne value (for>,=...), a list (forIN,ANY,ALL), or rows (forEXISTS)Visits above the average billSELECTOne value for each rowEach department's share of the totalFROMA table of rows, with a nicknameThe average of the doctor totalsWhat I learnt overall
TopicIn one lineA question it helped me answerWindow functionsDo maths across a group without losing any rows"Rank visits by satisfaction", "days since the last visit"JoinsCombine tables using matching IDs"Show each visit with the patient's and doctor's names"CTEsName the steps of a query so it reads top to bottom"Which doctors billed more than the average doctor?"SubqueriesA small question inside a bigger one"Which visits cost more than their department's average?"#sql #postgres #beginners #database #software #coding #development #engineering #inclusive #community
Introduction to Databases and SQL with PostgreSQL: Window Functions, Joins, CTEs and Subqueries on a simple Hospital Database. -
-
@26pglt
I understand where your words (feelings) are coming from and I heartily agree that no one in an abundent and rich country like ours should be homeless. #Homelessness is a failure of care by our society, and in its representational duties, our successive govts.But you draw the wrong conclusion by invoking #Defence expenditure which is a *public good* service (where privatisation has no place) like public transport, healthcare and emergency services.
Your case for the reprioritisation of public expenditure to be first and foremost directed to the *public good* such as social housing , emergency housing, Health, NDIS or UBI would be much stronger if rent control, tax reform and/or an end to fossil fuel subsidies, amongst others, were your targets instead. Because reform in those sectors of the economy do not impinge on any funding of public services for the social good (except in a positive way).
If you wish to argue against the cost of funding Defence as a *public good*, than that’s another thing altogether. But you’d want to be well prepared to argue that case.
I apologise if my toot causes you any anxiety or harm, that is not my purpose here. I just wanted to point out the difference between spending on the *public good* and abetting #Grifting, and where the govt can make a diffence to society as a whole by favouring the former and eschewing the latter.
-
#FT Citi becomes latest US bank to restrict AI agents from accessing accounts https://www.ft.com/content/f49c8e39-f07f-4177-9948-aabe55e5ed91?syn-25a6b1a6=1
"US lenders fear chatbots could shift billions of dollars in deposits to higher-yielding accounts"
Indeed they do. That's what I'd want my agent to do anyway. And why would I leave my money in an account that my agent couldn't access?
-
YSTALYFERA: Swansea Valley voice actor scoops three awards in one night, including the judges’ own pick
A voice actor from Ystalyfera walked away with three trophies in one night at the VOX Awards, including the judges’ own pick.
Phil Rowe won Male Voiceover Performance of the Year, Best Human Performance in a Corporate Presentation and the Judges’ Choice Award at the ceremony on Saturday 10 October.
The VOX Awards, now in their 32nd year, cover performances across the professional voiceover industry. Fourteen awards were handed out on the night.
Rowe says five of them went to three Welsh voice actors: himself, Jess Nesling and Nikki Mills.
‘It’s still sinking in’: Phil Rowe’s triple at the VOX Awards
“I wouldn’t have dared to dream that I’d walk away from the VOX Awards with three awards, including Male Voiceover Performance of the Year and the Judges’ Choice Award,” Rowe said. “It’s still sinking in!”
Two of his three awards came from a project with Cardiff-based CREO, which did the creative direction and scriptwriting.
“I think it demonstrates just what we can achieve when talented creatives come together, and how much potential there is to build a strong, successful creative industry right here on our own doorstep,” he said.
It’s his second big win this year. In May he took Best Male Performance in Audio Drama at the One Voice Awards for a production he wrote and produced himself.
He works from his own studio in the village, and one of his audio dramas, the horror series YstalyFEAR, takes its name from Ystalyfera.
Swansea roots and a Welsh accent in a Roman video game
Jess Nesling won Female Voiceover Performance of the Year. Her entry included her Welsh-accented character Voada in the video game Anno 117: Pax Romana.
“I’m really proud of my Swansea roots,” she said, and getting to show off her Welsh accent work in her submission was “particularly special”.
“I’ve never been in this for the awards, but it’s wonderful to have work I’m so passionate about recognised by people in the voice industry whom I admire and respect.”
A Pembrokeshire murder mystery and a studio called Elizabooth
Bridgend-based Nikki Mills won Best Audiobook Performance for The Caldey Island Murders, a murder mystery set in Pembrokeshire and her first full-length audiobook.
“It just felt completely right that my first foray into such a challenging genre should be set in my homeland,” she said.
Emily Jardine, who recently moved to Bridgend, also won, taking Best Human Performance in Live Events. She records in a studio built beside her house, which she has named Elizabooth.
For Mills, the best part was cheering on the others. “Five out of fourteen awards going to Welsh voice actors is quite some going,” she said.
Related stories from Swansea Bay News
YSTALYFERA: Voice heard in 150 million homes worldwide, Swansea Valley actor wins Best Male Performance at major UK voice acting awards
Phil Rowe’s One Voice Awards win in May.Ystalyfera
#SwanseaValley #voiceActing #voiceActor #YstalyFEAR #Ystalyfera
More news from the Swansea Valley village. -
Dark Academia – How Universities Die, by Peter Fleming
I ordered this book from our local bookshop a while ago but have only just found time to finish reading it. It’s an analysis of the decline of the neoliberal model of the university accompanied by the rise of commercialisation, managerialism and authoritarian models of governance. At 165 pages, not counting the notes and references, it’s a concise and compelling polemic, full of controlled anger and sprinkled with sardonic wit.
The problem I have in reviewing it is that almost every page contains something that rings so true that I’d want to include a quote. There wouldn’t be any point in doing that as I’d end up quoting almost the entire book, so I decided just to include one excerpt, part of a paragraph in the introduction:
Impersonal and unforgiving management hierarchies have supplanted academic judgement, collegiality and professional common sense. in many institutions, senior executives have no PhDs and have been trained in business or the military instead. Mindless performance targets dominate teaching and research to the point of caricature, designed by functionaries who’ve never taught a class or written a research article in their lives. Unfortunately, these hierarchies have become notoriously bossy. Coercion rather than volition compels much academic labour today, even tasks that scholars would have otherwise done willingly because it’s central to their vocation. This surfeit of duress, most of which is unnecessary and counterproductive, is a defining feature of the corporate university.
The book charts the rise of this fanatical managerialism, the consequent fall of academic institutions as places of education, scholarship and research, and their replacement by soulless teaching factories. I’ve been an academic for almost forty years and I’ve seen this process unfold in real time. As I approach retirement, I deeply regret that I didn’t do more to stop it.
Dark Academia was written around the time of the Covid-19 pandemic and published in 2021 so it does not cover the dreadful collapse of higher education in the UK since then. The author does make some presciently pessimistic comments, but the reality has turned out to be far worse than he imagined.
My own experience of the pandemic here in Maynooth illustrates what went on to a greater or lesser extent throughout the university system, namely that it was seen by Management as an opporunity to strengthen its grip. Staff workloads went through the roof as under-resourced departments struggled to deliver adequate teaching. Seeing that we were on our knees, the powers that be decided it was an ideal opportunity to kick us in the teeth. Witholding replacement positions for two lecturers who left at this time, showed a callous disregard for staff morale and student education alike. Needless to say, the person responsible was promoted.
Since the end of the pandemic, the Management of Maynooth University has been led frog-marched down the path towards Dark Academia. The past few years have been a jamboree for senior administrators, with the creation of umpteen new Vice-Presidents on high salaries and extensive perks, such €500,000 on a luxury chauffeur service and €1M. This profligate expenditure on management bloat has been accompanied by austerity everywhere else, especially for teaching and research. This new configuration of the University is clearly unsustainable. The only question is whether anything can be salvaged from the wreckage when it crashes.
The term The University used to have a collective meaning, referring to all staff and students, not just the leadership, but now it simply means The University Management. For example, it was rumbled last year that Maynooth University had paid €47,000 tax on the President’s behalf. The President eventually paid the bill and an anonymous spokesperson said “The University now considers this matter resolved”. I know I’m not the only person working at Maynooth who doesn’t consider it “resolved”: for one thing, I’d like to know who made the decision to pay the President’s tax bill in the first place; and for another I’d like to know who issued the statement that it was “resolved”. I mention this episode because it exemplifies a phrase used by Peter Fleming at the end of his book. Academics used to be able to say that that they worked “in” a university. Not any more we don’t. We work “under” a university.
I’m not very optimistic that academia can change for the better – I think it’s too far gone – but this book at least identifies most of what is wrong. Perhaps, if enough of us read it, we can start trying to reverse the decline.
#DarkAcademiaHowUniversitiesDie #education #MaynoothUniversity #NeoliberalUniversity #PeterFleming -
How I managed to prolong my 2025 tax-year ordeal
Normal people had their 2025 federal taxes filed no later than April 15. Abnormal people–to be nicer about it, those with more complicated tax scenarios or who succumbed to cognitive overload in early April–filed for a free six-month extension and paid what they estimated they owed by April 15, then completed the rest of their tax-prep tasks by Oct. 15.
And then there was me.
Despite years of practice at filing for an extension and using that bonus time to get my returns right–it’s become all but a default setting over 15-plus years of freelance life–I needed two extra days to conclude my 2025 federal tax year.
I knew things would be more complicated this year because of Intuit–specifically, the TurboTax developer removing support for the business-essential Schedule C from its “Do It Yourself” tier and instead asking me to pay $199 for an “Expert Assist Premium” offering that would have almost doubled my costs over last year’s.
I was already salty at the company for its history of trying to stop governments from competing with TurboTax, including bitter resistance to the IRS launching its Direct File app1 under the Biden administration. So I decided to take my business elsewhere and opted for FreeTaxUSA, an Editors’ Choice pick at PCMag.
Per the name, this service of Provo, Utah-based TaxHawk charges nothing for federal returns–even those complicated by investment and small-business income–and charges just $15.99 for state returns2.
For the most part, FreeTaxUSA proved to be an adequate replacement for TurboTax. It made three mistakes when scanning PDFs of tax forms that I’d uploaded and balked at doing anything with a fourth, but those glitches–in areas where I’d want to double-check the numbers recorded anyway–were easy enough to fix.
I also found it weird that FreeTaxUSA didn’t suggest how much to contribute to my SEP (Simplified Employee Pension) IRA to minimize my tax bill. But since I had decided to step through my returns in TurboTax in a separate window to be sure I didn’t miss anything–which I’m not saying is a great life choice for everyone–that was easy enough to fix.
This extra work ensured that I’d need to file an extension, so I once again did that and then behaved as foolishly as ever by ignoring both apps until early October. An evening of work last weekend revealed some inconsistent Sched C data entry that, when fixed and even after triple-checking, left me owing slightly less in FreeTaxUSA’s math than in TurboTax’s.
Just in case, I decided to wait until Thursday, after returning from a brief work trip to Dublin, to give my return one final look before e-filing it via FreeTaxUSA. I clicked the “Send Tax Return” button with several hours to spare…. and then got an e-mail that I did not expect or enjoy: “Action Needed! Your 2025 Federal Tax Return Was Rejected.”
That message offered two explanations that conflicted somewhat: Either “the Social Security number or last name entered for a dependent doesn’t match their records” or “a dependent’s Social Security number has already been used as a dependent on another return for the 2025 tax year.”
The first possibility could not possibly be true, I thought, while it seemed quite plausible that our kid had joined us in being a victim of a data breach. I dug into the IRS help page on this topic and saw that it cited the same two reasons and advised that if I had the dependent SSN correct, I could e-file again after adding an Identity Protection Personal Identification Number (IP PIN) to my account.
But when I tried to create an IP PIN Thursday evening, the IRS site reported a “partial outage” that locked out that option. It was then too late to call; phone support only runs from 7 a.m. to 7 p.m. on workdays3.
If you’ve been reading this and thinking “why didn’t this bozo double-check his kid’s SSN?”: I did, ineffectively. With jet lag now fogging my head, I did not notice that the final four digits only had three of the right numbers, only one of which was even in the right position. Stupid, I know.
Having also learned that the IRS allots a five-day “Transmission Perfection Period” to correct a rejected e-file attempt, I gave up Thursday night.
In the middle of a work-clogged Friday, I set an IP PIN on my account and saw that it, too, didn’t fix things… and then finally, on Saturday I thought to reach again for the folder in the filing cabinet holding everybody’s important documents.
That revealed that I, accounting genius, had our teenager’s SSN wrong in the return and in the secure note in 1Password from which I’d copied those nine digits months ago.
I fixed that, and the e-file sailed through. Then I sent a text to my wife reporting this success, followed by this self-eval: “don’t I feel like a dumbass now!”
This is one of my more embarrassing cases of user error, but if I document it maybe somebody else won’t make the same smooth-brained mistake. This has also reminded me, somewhat painfully, that any important number is worth double-checking–especially if, like me, you’ve caught yourself transposing digits often enough to wonder if you’ve had dyscalculia all along.
- The Trump administration killed off Direct File last year, and I don’t think Intuit mourned its demise. ↩︎
- As much as I hate the idea of paying anybody to file Virginia’s relatively simple state income tax returns–the commonwealth being an early sucker for Intuit’s crony-capitalist lobbying–filing our 2024 state taxes on paper became a bureaucratic odyssey that I don’t want to repeat. ↩︎
- DOGE cuts whacked the IRS workforce, but its phone-help hours were the same in 2024. ↩︎
-
Next 16 generates a 146 line routes.d.ts with every route in the app as a string literal. What that buys you (params you cannot misspell), what it does not (searchParams, Link hrefs), and the 50 errors you get when the file is missing.#nextjs #typescript #webdev #react #software #coding #development #engineering #inclusive #community
A union of 52 route strings we never wrote, and three of our seven layouts all claim the same one -
Mozart Trumpet Concerto in D 🎷
#WyntonMarsalis
https://www.youtube.com/watch?v=OKDcWyJaaoc -
I'd been wondering for months why #Thunderbird happily switched between light and dark mode based on my GNOME preferences, but #Firefox did not.
TIL: Firefox has `browser.theme.content-theme`, which (if set to `2`) means it selects light and dark mode based on your desktop environment's theme preferences.
But it also has `ui.systemUsesDarkTheme` which, if it exists and is set to `1`, ignores system preferences and pretends they are set to dark. Delete that option, and it behaves as expected.
-
Heilerziehungspfleger (m/w/d) in der Begleitung und Assistenz in Fehrbellin
-
Waiting, I'd been waiting so long for this
Waiting, all my life had been hit and miss
I knew in my heart this was only the start of a new life -
Gilded Winter Grove
Available here...https://tina-lecour.pixels.com/featured/gilded-winter-grove-tina-lecour.html#nature #trees #naturelovers #mastoart #fediart #fedigiftshop #creativetoots #wallart #art #artist #arte #artforsale #artprints #fineart #homedecor #interiordecor #puzzle #painting #gifts #giftideas #puzzle #buyintoart #ayearforart #mastodonart
-
Ab 2027 startet in Deutschland die d-you Wallet, mit der digitale Identitäten, Ausweise und mehr sicher per Smartphone verwaltet werden können. Laut Bitkom wollen 53 % der Deutschen dieses zukunftsweisende System nutzen – ein spannendes Feld für IT-Profis und Entwickler!
#Aktuell #StudienUmfragen #Bitkom #EUDIWallet #Wallet #Audio
https://www.it-finanzmagazin.de/bit...
https://www.it-finanzmagazin.de/bitkom-mehr-als-die-haelfte-wollen-d-you-wallet-ab-naechstem-jahr-nutzen-252377/?fsp_sid=47151
