So in $dayjob I have used an 8-core (16-thread) CPU to 100% regularly, as well as fully utilising 64-GB of RAM. I would be able to use 16 cores, 32, or more.
Random examples:
Trawling through 70 GB of web server log files going back years with ad-hoc analytics. I tried ingesting this into a cloud service but it was taking forever, and cost a non-trivial amount of money. It was also dog slow compared to just doing it locally with SQL Server Clustered Columnstore.
Compiling Chromium. I'm not even a browser developer or anything, but there was a "global" change that I had to preview ahead of time, so I made a custom Chrome build that had the change embedded in it. I also made a matching build without the change to enable A/B comparison testing.
Server base image task sequence development. You would think a server is ideal for this, but no! It's very I/O heavy, and doing it locally in Hyper-V using an NVMe drive can make this 4-5x as productive as any shared platform with throttled disks. There are zero cloud services that can even approach a Samsung Pro 980 SSD for reasonable costs.
Multi-server lab tests. Compared to cloud services, it's faster and easier to build a local pool of machines for testing how they interact. With 64 GB of memory, I can (and have) easily run 20+ machines. Compared to cloud platforms, I can do things like thin-cloning to build (and then delete and rebuild) these nearly instantly -- about 5 seconds or less in some cases. Even the relatively heavyweight Windows Server 2022 VMs boots in just 1.4 seconds! Some build jobs require nearly 100 GB of content to be deployed, and the upload to the cloud is a problem in some customer environments. E.g.: due to security restrictions. Doing it locally is easier and faster.
Photography and video editing. I do both as a hobby. Lightroom will cheerfully use whatever number of CPUs (and GPUs!) you have to process images. My recent hardware upgrade made a very noticeable difference to the workflow. Similarly, photogrammetry and other advanced 3D modelling workflows can use up virtually any amount of compute.
Password cracking. I occasionally need to reverse a password hash for work, typically to fix lost access to some ancient piece of network gear. Sometimes it's for verifying password strength where the plain-text password is not known. I could distribute this across a pool of cloud VMs, but my lab subscriptions have hard limits and this is sensitive data.
Automated searches through code for a breaking change. E.g.: scripting the checkout, build, test, etc... in a loop. (Git bisect doesn't solve every problem)
VS Code now has ripgrep integrated, so "find in files" will use every CPU. I have about 100 GB of files on my laptop from various old projects, so this is a very helpful feature for digging up some code snippet that I only vaguely remember writing. It can find a match for a regular expression at 700 MB/s using 16 threads at 100%.
Defragmenting moderately large (50-500 GB) databases. I have some customers that have built up horrendously fragmented databases over decades, but can't afford the IOPS and/or downtime to fully rebuild all of the tables and indexes. I recently utilised the aforementioned Samsung SSD to defragment an 80 GB database in just minutes, that would have taken days or weeks on the production server. Again, transferring this out to a cloud is a no-go, and the problem was that typical server storage is much slower than a typical laptop. Locally I could get 200K IOPS sustained. SQL Server Developer Edition is the same as Enterprise, and will utilise all cores to rebuild indexes in parallel.
Etc, etc...
If you know what you are doing, you can make the CPUs go "brr...".