Metabase is a great product. Simple to deploy and use, stable, easy for non techies and SQL analyst types. It tends to be my go-to.
Superset is a great product and we are lucky to have this quality of BI tools for free. It does however have a few extra rough edges and quirks where your query doesn't render as you would expect, especially with niche databases such as Druid and Clickhouse (via a third party driver). I often find myself checking log files to see what actual SQL was issued from the front-end. It's also slightly more intimidating for end users too.
Metabase is easier to deploy. I use a single binary vs (I think) a docker compose setup for Superset.
Both have a cloud offering, Preset.io is the Superset one which we are just using now and having a productive time with.
I would look at both Metabase and Superset before the heavier weight commercial options if the choice was purely down to product features. Tableau, Looker et al don't seem to be bring much to the table above these, and they are both quite difficult and expensive commercial organisations to deal with.
With all of this said, building simple reports and dashboards over aggregated data is a fairly commodity task nowadays. Unless you are doing anything funky, all of these tools and tens of others will do the job.
These extend to the user interface and ux. Unless you need the more sophisticated analytics features of Superset then I'd definitely start with Metabase.
While Metabase is not the most sophisticated tool out there, we had great experience onboarding users that only knew Excel before. Which resulted in them answering the majority of basic questions about data themselves, reducing the load on the BI/data staff.
PowerBi pricing($10 per seat) is so nice compared to Tableau($85 per seat last I checked).
Then I realized that Metabase and Superset both can be run on promises free of monthly costs. That can be huge when the number of users fluctuates greatly.
Superset and Redash are great for analysts and technical users looking over more control of the output, with no expectation that the end user consuming the data will want to "drill down" further or satiate their own curiosity. But you can pull things off with these tools that Metabase isn't built for.