Next query, for median calculation was a window function query.
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c1)
OVER (PARTITION BY (SELECT 1)) AS MedianCont
To my surprise, the performance was even worse, and at this time, I have to say, I was running this on SQL Server 2017 with CU7. But luckily, I had a SQL Server 2019 CTP 2.0 also installed and here, with no further optimization the query ran little over 1 second.
I’ve warned people away from this function for all but tiny data sets because of how poorly it performs. With SQL Server 2019, I might be able to recommend it.