No direct experience here, but I worked with a student who is going to an Ivy for a D1 sport. This student has good, not stellar grades, and a below average test score for that school. He/she wrote an excellent essay. Certainly good enough to be “waitlist worthy” by the standard you give. How much did it help? I don’t know, but it didn’t hurt. The college was very clear that they cared most about grades and the student had to keep them up.
If you are looking at academically rigorous schools and submit a mediocre essay, it probably won’t do you any favors. Aim for a decent essay. It’s not that hard to do. There are many ways to get help, including reaching out to teachers and utilizing this website. You can start by reading some of the pinned posts in the College Essay forum.