I've seen a lot of marketing, movies, series... But what is this game about?
The broadest strokes are that it's about a set of seven magi who are selected to take place in a winner-takes-all, battle-to-the-death conflict over a wish-granting grail. In this fight, each magi summons a superpowered familiar, each of whom's identity and powers are based off some mythical figure from legends, folktales, and actual history. The primary POV character is Emiya Shirou, who is unwittingly selected by the grail to fight despite barely knowing anything about magecraft or its place in the modern world.
Part of what makes Fate/stay night compelling is its presentation. Much of its lore and character building can't feasibly (or, easily at least) be communicated given its nature as a story of a bunch of people trying to kill each other. As such, the author Nasu split the story into three routes, each covering the same time periods and set apart based off the player's choices for Shirou's actions in the novel, and each route unpeels more layers of the onion to reveal character motivations, explain more of how the world works, and get to the core of the conflict. (PS: The novel's designed in a manner such that you can't make certain choices until you've completed prior routes, so there's no worry about running off track or doing something out of order)
Fate/stay night is not without its problems (and it does have problems lol), but if you're a lore junkie who also likes an anime aesthetic and are intrigued by the structure I described, you might enjoy it. Edit: And each route has a principal waifu, which is a pretty heavy element of the marketing.