Mostly, I'm using it for ad-hoc querying as it allows for semi-automatic columns detection. For example, my bank account file I am querying shows how I'm spending my moneys. It looks like below and I have few more advanced queries doing some different calculations on my CSV file.
select ExtractFromDate(DataOperacji, 'month') as 'Month', ExtractFromDate(DataOperacji, 'year') as 'Year', SumIncome(ToDecimal(Kwota, 'pl-PL')) as 'Income', SumOutcome(ToDecimal(Kwota, 'pl-PL')) as 'Outcome', SumIncome(ToDecimal(Kwota, 'pl-PL')) - Abs(SumOutcome(ToDecimal(Kwota, 'pl-PL'))) as 'Total' from #csv.file('C:\some\path\my_account.csv', ';', true, 37) group by ExtractFromDate(DataOperacji, 'month'), ExtractFromDate(DataOperacji, 'year')
from another point of usage, I'm sometimes using it to find files in folders as I can both query file properties and their content. Also, I was testing it with querying git repository (I was using https://github.com/libgit2/libgit2sharp), If you are curious about some statistics, then probably it would be usable for you.
Another thing I would imagine is that when you have such a huge compressed file that you can't unpack, then you could query such a file and unpack only what you needs. It doesn't have to be a packed file only. It can be such a big log file that most of editors wouldn't be able to open.
Another thing, If you are an administrator and have to move buch of files or create yourself a view how the files looks in more aggregated way, then it will be probably good for you (I'm not an administrator and never was so please take into consideration that my point of view may be clearly wrong)
Different thing, If you've got a bunch of IOT devices, you would end up writing plugin that gets data from such devices and provides you rich statistics about various things.
Another possibility, you need event (for scheduling purposes) that fits some sophisticated time pattern, you can't use CRON becouse it's syntax has some serious limitations, probably it will be better to query time for such a pattern and store it somewhere. Otherwise you would need writing a lot of single CRON expressions that all fits your requirements.
I will go right now into some crazy imaginations like:
You would end up writing a plugin that extract some statistics from photos (like is the image contains people, how many of them are, what are the color of sky, does it has sun?) and then query some huge photo sets about how many of images contains people and also have a sky as a background). It should be really fast as your plugin can use some C++ library to fastly process images. Because you are fully controlling with what and how you feeds evaluator, you can use parallelism to process multiple files at once :)