Jesse's Blog

The examples provided in "Learning Postgres" for Window Lag/Lead functions in Chapter 6 really don't do a good job of selling you on expending the effort into learning them. Here are some better ones.

Let's say you want to align today's sales totals and yesterday on the same row.

SELECT 
    sale_date,
    sales_amount,
    LAG(sales_amount, 1) OVER (ORDER BY sale_date) AS previous_day_sales
FROM 
    daily_sales;

.. or, you want to know how much more or less today's tally ended at.

SELECT 
    sale_date,
    sales_amount,
    LEAD(sales_amount, 1) OVER (ORDER BY sale_date) - sales_amount AS next_day_difference
FROM 
    daily_sales;

This one is a lot of fun. I have a list of dates. I want to know if there are days missing in that sequence.

SELECT 
    sale_date,
    LEAD(sale_date, 1) OVER (ORDER BY sale_date) AS next_sale_date,
    LEAD(sale_date, 1) OVER (ORDER BY sale_date) - sale_date AS gap
FROM 
    daily_sales;

So today I learned that there's a -c parameter on the md5sum command:

md5sum -c checkfile.md5

This will use the output from a previous run of md5sum piped out to flat text to validate that the files defined in that file match. Sounds simple if you took the time to read the man page. I hadn't, and so now I have to laugh at myself for the oddball workarounds I've done after not having read it.

Same applies to a recent read of 'Learn PostgreSQL' by Ferrari and Pirozzi. I read about Window functions, but when I originally read about rank(), I stopped reading, when it turns out that first() did I wanted with less lines of code.

So what I really learned (for probably the ten thousandth time) is don't stop reading.

Interesting Read.

Ladies and gentlemen, I give you the Doom Scroller.

This is a Raspberry Pi Pico, a breadboard, a rotary encoder, a button, a suction cup, and an Altoid tin begging to be released from this mortal coil.

I got the idea from MaxMacSTN trolling for 3D prints. I've extended his code a bit such that it does this:

In my config for I3, F13 is the start for ncmpcpp, and Win-D is Rofi. You switch modes by holding down the knob for a second.
For now, the button on the side is a reset. If you hold the knob down while doing it, the Pico allows you to modify the code it runs. Mode 1 also functions well as an SDR tuner.

I have three more encoders, and I'm strongly considering adding some function before finally designing and printing something a little more permanent. I gotta tell you, doom scrolling this way is a dopamine well.