Even though the Sith Eternal and the First Order were defeated in the Rise of Skywalker, I suspect we have not seen the last of either of them. The Sith themselves may be gone, but they have left behind a dark legacy. When the Galactic Empire collapsed, there were Imperial remnants that remained active throughout the galaxy. It is possible that the same thing will happen with the First Order. Even though the Final Order’s fleet of Star Destroyers were demolished on Exegol and the First Order was suffering losses everywhere else, there are bound to be fleets and bases that will survive.
In addition, even though the Sith Eternal was destroyed along with Darth Sidious, there is no guarantee that all the cultists died. Even though Exegol was their headquarters, it is possible that they may have bases on other Sith worlds such as Moriband. Also, if there are some cultists who are Force-sensitive, they may learn to use the dark side on a similar level to the Knights of Ren. They won’t be Sith, but they would still be a potential threat to future Jedi. If even one cultist survives, the Sith Eternal will inevitably start anew.
If there are remnants of both the First Order and the Sith Eternal, it is possible they may combine their strength. They won’t be as powerful as they were when their alliance was first formed, but they would still serve as a threat to the galaxy at large. I think it would be interesting if this is what happens in the aftermath of Rise of Skywalker.