SQL is just inherently repetitive. What I would do with macros is start with a simple list of column names and turn it into a statement updating them all, or a join, or whatever I needed. Eg. a macro could turn "FirstName" into "a.FirstName = b.FirstName", and then work just as well on "LastName".
In other programming I don't have particular favorite macros, but still find small repetitive bits here and there that can be turned into temporary macros. Say for example you have ten variables that each need to be reset to zero. Don't type all the resets, just type the variables, turn one of them into a reset while you're recording into macro w, then type 9@w to do the rest.
Copying up to the second f is just a silly example. It could maybe occur though if you were manipulating lines in a data file. You look at it, see that on each line you want to do something with everything up to the second f, so you make a macro. You don't save it for general use.
In short, macros aren't for major repetition that should be factored out, they're for the minor repetitive edits which, if you're not using vim, you might not even think of as repetitive. Sometimes they're useful for refactoring.
So no, I'm not using macros "without even thinking" and this would probably be a more productive discussion if you didn't make such assumptions.