I haven't watched Picard but I noticed a difference in the ST: Discovery 1080p SDR episodes vs 1080p Dolby Vision...not a night/day difference but if given a choice the easy call is the one with HDRSpeaking specifically about Picard, that show is not actually graded in HDR. There's nothing in the image that extends beyond typical SDR range. The "HDR" version through Apple is just SDR encoded in an HDR container. I compared directly against the SDR version through the Amazon channel and saw no difference at all in brightness or contrast. CBS may not even be aware that Apple is encoding it that way.
What's happening with Showtime content, I can't say. The Showtime Anytime app on Roku gave me SDR for City of Angels. I haven't tried the Apple channel.