While the Screen blending mode makes it essentially not appear because it blends onto an already white background, Overlay allows some of the sunset tones to blend through, which can be enhanced by boosting the saturation.
This makes the light areas of the sunset layer appear nicely vivid, but it has a negative effect on the darker parts. Instead, we want the sky from the overexposed layer to show through so the overall composite is not so harsh.
I was able to accomplish this by leveraging the lightness of the overexposed frame as a layer mask for the sunset tones:
This helps cut away the foreground palm trees and terrain and gives a gradient to the sky. It essentially constrains the sunset to where the sky is bright in the overexposed image. The darker regions, where the desired vivid sunset does not appear, get hidden and allows the overexposed frame's terrain and sky to show through.
But because the streak from the rocket launch and the medium brightness tones halfway up the image from the overexposed layer mask are not relevant to the sunset itself, this masked sunset layer can have a second layer mask applied to it as a gradient:
This masked result has an Overlay blending mode and a saturation adjustment layer exclusively applied to it for the reasons explained above.
Next, this masked sunset can be simply placed on top of the unmodified oversaturated sky layer to give it a more vivid sunset sky:
This preserves the exposure and details of the original rocket launch long exposure, but augments it with the attractive colors of the sunset captured beforehand with a shorter duration exposure.
With some finishing touches (restoring the unstreaked moon from the short exposure, applying a black gradient to darken the upper sky, and some subtle color corrections), the composite is done!