Milder cheddars are for melting. They get used in things such as grilled cheese sandwiches, grating into chili, nachos, or in quesadillas. In these uses, they don't need the full-bodied flavor of a sharp cheddar, but they do need to melt down into soft, gooey, creamy deliciousness.
Sharper cheddars are for flavor. I commonly see them in sandwiches, macaroni and cheese, topping salads, and as part of cheese boards.
The reason it works like this is that "sharper" cheddars get their bite from additional aging, which develops a richer flavor but makes the cheese drier and increases the melting point.
That said, there are no hard and fast rules for how to use mild vs. sharp cheddar. In my house we always use extra sharp cheddar because even everyday cheese should be a gustatory delight. It's a little trickier to make into a quesadilla or melt on a cheeseburger, but the extra tang is worth it!