At the company I work for we use it to encode the scripts (as in screenwriting) of interactive fiction, which allows us to have a nice schema to define information about characters, dialogues and other game elements.
I also use it as the backing database for my personal website, sort of as a CMS. I'm also making an e-commerce site for my dad that will use it to store product, client and order information.
Wintergatan (of Marble Machine fame) has a team of people collaborating on making a digital CAD version of their physical machine, and they use Airtable for task management and as a structured wiki.
I do wish that Airtable supported some other things, but it does fill a good niche where you need a more organized and connected spreadsheet with a convenient way of visualizing data beyond charting.
Google Sheets works fine, but there is no alternative for Airtable's record links, which is how one expresses relations between tables. You can get data from other tables, yes, but more often than not range syntax breaks, or someone puts the wrong data type somewhere and it's tricky to debug. For me, the main selling point of Airtable is record links and typed columns.
I don't think Airtable will be replacing general purpose spreadsheets anytime soon, nor can it handle very math-y sheets well either.
