I don't see any good answers here so I'll give it a try.
Smart contracts can be used to build voting systems, multi-signature agreement systems, escrow systems, exchanges etc. But all of these rely on data being in the crypto world e.g. on blockchain.
The most powerful emerging use case for smart contracts is verifying zero knowledge proofs. Using groth16 or PLONK you can compress any amount of information or computation into a constant size proof (constant in both size and verification complexity [1]). This leads to the question, what is the use case for zero knowledge proofs?
TLS notarization: a user can prove they received data from a website by proving the signature in the TLS session. So e.g. i could prove how many twitter (sorry, X) followers i have by proving an element in the HTML that is signed by twitter, or prove that i have a dm with individual X (not the company, a variable meant to indicate some person). This can be extended to proving e.g. bank account balances using TLS signatures. The idea is such a TLS proof can be ingested on the blockchain so anything on the internet can be used as a logical condition for a smart contract. https://tlsnotary.org/
^ a similar case exists for email data verification using RSA
Private user data: companies can track information about users without knowing what information belongs to what user. The idea is, the user data is stored inside a ZK proof and the user manipulates the data in ZK, then provides a proof to the web application that they manipulated it in a way that follows the rules defined by the application. A simple example might be ZKFlix. Each time a user watches a movie they add an entry to their data indicating `moviedId: true`. The web application can store the user state without knowing which user watched which movie. Put more simply, each change to user data is attributed to an anonymous actor. Theoretically it should be possible to build websites with the same functionality of existing websites, but where the website is non-custodial of the user data (this isn't strictly blockchain related). This type of system allows users to make proofs about their application user data and submit them to the blockchain.
^ the more general case is building a state system that exists entirely in ZK and putting a state root on the blockchain. Then anything about the state system can proven onchain
These are the examples I have off the top of my head (though i do work in this space). I think smart contracts by themselves lack functionality and resort to hacky things like permissioned oracles. Combined with ZK though smart contracts become a financial system that is trustlessly bound to the internet. The hard part is making the internet provable as sequences of polynomials.
Hard agree that the current user experience sucks though. I'm of the opinion that in the future users won't directly interact with the blockchain the same way a user doesn't interact directly with e.g. postgreSQL. If to make an account on a website you had to write an SQL query inserting the row that would be a similarly bad experience to managing your own private key xd
[1]: The scaling isn't strictly constant, but small enough to be considered for practical purposes constant