You can check this with a flag "appended"

window.appended = false; // set as global variable
$(window).scroll(function() {
   if($("#container").hasClass("active")){
        $("#container-wrapper").addClass("active-exists");
        if (!window.appended){   // check with global variable
             $("#container-wrapper").prepend("<p>Test</p>");
             appended = true;
        }
           
   }
   else {
        $("#container-wrapper").removeClass("active-exists");
        $("#container-wrapper").remove("<p>Test</p>");
    }
}

Instead of checking whether the element has a specific class, check whether the y-axis scroll position is 0. You'll also need to store whether you've appended the element in a variable, so as not to keep on appending on scroll.

let elem = $('<p>Test</p>')
var hasAdded = false;
$(window).scroll(function() {
  const scrollY = window.scrollY;
  if (scrollY == 0) {
    $('#container-wrapper').removeClass('active-exists')
    elem.remove()
    hasAdded = false;
  } else {
    if (!hasAdded) {
      $('#container-wrapper').addClass('active-exists').prepend(elem)
      hasAdded = true
    }
  }
});

html,
body {
  height: 300%;
}

#container-wrapper{
  position:fixed;
  height:50px;
}

.active-exists{
  background-color:yellow;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div id="container-wrapper">
  <div id="container">

  </div>
</div>