Apart from Fred I've done probably more night trains than anyone else here- in the USA, Canada, Australia (the Indian Pacific), Russia and every Western European country.
It's interesting that the UK ones are mentioned above, because when we moved from the Mark 3's to the Mark 5's we lost a lot of the charm in the move to a higher grade, but more functional new train cars, the service level also changed. I actually remember the Mark 3's coming in, the excitement there was and the huge step upwards in safety they were from the Mark 1s. I was a season ticket holder at one time on the UK sleeper service, travelling 2 or 3 times a week from London to Manchester via Glasgow- largely due to an idiot chief accountant who didn't want to pay peak morning fares, so the sleeper overshooting Manchester by 200 miles was then (it isn't now) by far the cheapest option (not to be fair did I want to be up at 4.30 am to be in Manchester by 9am). By then we had lost the London to Manchester/Liverpool/Preston sleeper so via Glasgow was the least worst option).
The only time I've shared a cabin was on RZD from St Petersburg to Moscow- a very untypical Russian route because going back to the time of the Tsars it was a premier night train route with stiff competition as to which was the best train, also Intourist refused foreigners to use the more basic alternative trains. Nor was it sharing with strangers as it was a tour party, so not a true sharing experience. The overwhelming thing about that service was how warm the Russians like their trains. On a quality train without opening windows that was a bit stuffy.
Otherwise I've always had either a seat or a private cabin.
Pretty much it has always been a positive experience. You get used to the movement, the stops and the shunting as the train splits and joins on route. And it saves a lot of time. At one time it also saved money- nowadays that is a fairly revenue neutral question.
The biggest change in Europe is the general loss of restaurant cars on overnight trains, and by far and away the aged Hungarian restaurant car on the service train the Orient Express to Paris was the best of them.
Overall I think the Norwegian night trains are probably the best in Europe, even better now with the lie flat beds option on the Bergen route (coming soon to Trondheim as well).
The best US route to my mind was the Coast Starlight (sadly now downgraded), the worst the Denver to Chicago (I was unlucky and got a train that was hunting all over the place- whether due to track quality, a defect or excess speed I don't know, but almost no sleep to be had).
Pretty much the last of the true Communist trains left is the Bucharest to Chisinau gauge changing train- a route I would love to do.
Another route I would love to do is the Milan to Sicily route, also I would love to do the Stockholm to Stockholm circle- ferry to Turku then the double overnight up through Finland, round the top, then back down through Sweden- I've done the Narvik to Stockholm.
Routes I missed- London to Syria (I never got it before a certain conflict) and London to Athens (I had a connecting ferry from Greece to Italy cancelled, and now the route has been closed since Covid).
And I was very sad last year not to get either of the Paris to Barcelona night trains to connect into a Transatlantic voyage to New York- I just couldn't quite make the timings work.