SQL Server - query optimization for highly skewed data distributions
Are there any other approaches to help optimizer deal with highly skewed data distributions?
  


Filtered statistics and breaking the query up using intermediate temporary tables (correctly!) are the main options, but you could also consider how an indexed view might be used to help. Properly implemented, an indexed view should have approximately the same impact as an extra nonclustered index on the base table(s).

Using WITH (NOEXPAND) instead of relying on automatic matching (Enterprise Edition only) will allow the optimizer to create and use statistics on the indexed view as well.

More generally, if you are able to identify the 'safe' or 'unsafe' values ahead of time, you could consider a hybrid approach (including dynamic SQL) as detailed in Building High Performance Stored Procedures by Kimberly L. Tripp.

You could also consider multiple separate procedures optimized for different cases, using the appropriate approach in each, including hints like OPTIMIZE FOR.

Finally, you have plan guides and/or forced plans via Query Store (where available).


  
  What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?
  


Mainly issues around the filtered statistics not being updated as frequently as one might hope. You can workaround this by refreshing these statistics manually.

You're already recompiling, so that should address the use of filtered statistics with parameterized queries.