I’m not sure how it works, but can you plug into two SC stalls with the dual charger and charge twice as fast?
No. On-board chargers are bypassed during supercharging and have nothing to do with supercharging. What you are suggesting would require a second port on the car to attach the second supercharger. Even if you could, the current cars couldn't benefit. Supercharging speed is already limited by what the battery can take. Doubling the available power won't help; current batteries are limited to ~120kw peak at low state of charge (SOC), and that rate drops as the SOC increases.
There have been sightings of the Semi charging on multiple superchargers at once (4 max, I think). They use a special combiner cable/extension cord that only works with the Semi.
Or if you went to a friends house for dinner and could only use 120v outlet, would it charge at the 120v rate twice as fast?
No, the limiting factor on 120V charging is the capacity of the plug. It doesn't matter if the car can draw 40A, 48A, 80A, or whatever if the plug can only supply 12A (or 16A for less common higher capacity versions).
There are no common outlets that allow you to charge over 40A (80% limit of a 50A outlet), regardless of voltage. Charging at these rates requires a hard-wired charging station. There are specs for 60A outlets that could, in theory, allow you to charge at 48A (80% of 60A), but Tesla doesn't make an adapter for them, the UMC is not rated to run that high, and the outlets are very, very rare (so no adapter will ever be made).
Or if SC stations are able to put out more than 40amps in the future, would this be beneficial? Curious as to the real world examples this could be a useful function.
Superchargers already put out way more than 40A. Remember, on board chargers have nothing to do with supercharging. Higher capacity on-board chargers do allow faster charging at high current J1772 and Tesla HPWC installations, but these are not common (at least not outside of CA). I've used 3 in 2 years (an 80A J1772 at a public garage in VT, and 2 open-to-the-public 80A HPWC's at office parks in NH and MA).