See also: When to use grep, sed, awk, perl, etc https://unix.stackexchange.com/q/303044
But I learned awk while sitting in an office at a client site. I forget the specific scenario, but I wanted to split up some files into some other files. I didn't even know awk, but grokked enough from the man page to let me do what I wanted to do. I can't even say what provoked me to turn to awk in the first place. I do know I ran into some internal open file limits, but worked around that.
If you want to tear files apart, or summarize them in some way, or push the fields around, awk is much better. sed is an editor. If I have a sed scenario, I'm more apt to just do it in vi and save the result than stitch together some pipeline with sed.
Most of my use cases are one off processing and analysis. I've never had any workflows that relied on awk or most anything like that. It was almost all throw away code, a tool on the workbench, not the production line.
(I have a set of scripts I use to parse NOAA's weather web page to plain text, and ended up resorting to both sed and awk in the process, and haven't yet tried to simplify that to a single script.)
Sed is usually used for simple text substitutions and manipulations.
Awk has built-in record and array concepts, as well as more standard programming constructs (loops, if/then, case/switch, printf, and external system interfaces (launching and/or reading from external programmes).
My view is that the tools overlap considerably, but also complement one another strongly.
However, sed has grown out of the command language used by the tty editors, and is more difficult to program (although it is Turing-complete).
The awk language implements much of the syntax of C, and it is not difficult to write a very slow and inefficient script. This inefficiency is harder to reach in sed, because it takes more effort to abuse it.
O'Reilly's book on sed and awk is available free online, both to browse and to download as a ZIP.
¹ https://blog.ivanristic.com/2015/02/apache-security-ten-year...