The solution itself comprises just three CTEs (common table expressions), that build on each other respectively and are numbered consecutively. The final result is computed after the fourth query and presented as the distillery_algo view.
CTE1 first generates a virtual previous value for each key and with UNION attaches it to the table. This is the very first version that was always valid (in this case 01.01.1900), and the first version of the key is actually valid up to the date_valid_from. A dummy value is set for the relevant attribute (category), which could not occur otherwise.
CTE2 is not complicated, but possibly a bit confusing: CTE1 is joined to itself via a key and validity date, such that each row finds its next direct value. Now you can see why the date_valid_from and date_valid_to must correspond precisely.
We rename the relevant attribute (category) versions: the current value (this) is defined as the previous value (prev_val), the next value (next) is set to the current value (this_val).
The current date_valid_to becomes the new date_valid_from; this way, the last version in time gets a virtual next value. Since this end dummy at LEFT JOIN hasn’t found any partner, the relevant attribute is replaced through the same dummy value at the beginning.
Perhaps the next steps will help you better follow the why and what for:
After this shift, in CTE3 we simply leave those rows unchanged in which the relevant attribute has changed (this_val <> prev_val), and where it so far does not concern the end dummy. For this, however, the start dummy remains (dummy value in prev_val).
For the final result, the Windows function LEAD() is applied to determine the date_valid_to from the date_valid_from of the next value, and this_val gets back the name of the relevant attribute (category). For the last version within the key, we set the final end date (e.g., 31.12.9999).
Fantastic! We’ve managed to distill an extremely smooth single malt whisky from the formerly changeable dates!