That really depends on the map and the amount of features it has, I will start with the biggest file sizes:
Campaigns can have the largest file-size: they contain multiple maps, which all need their own models, the gameplay can keep you seated for quite a few hours.
But yet again, it depends on what kind of campaign, if you want a quick campaign which lasts about an hour, don't make it too big.
However, campaigns have to be downloaded at sites, making it easier/faster to download.
Single player maps are second in this line, this can contain everything, since single player maps are usually of a higher quality (it needs to get your attention, since there aren't any other people to do that).
In general RPG's probably have the highest file-size, as long as it's below 10MB, it's not really a big deal here.
ORPG's can go up to 4MB, because it contains a lot of systems and a massive gameplay.
Once you have the map, you can play for a long time without having to wait for other people who need to download it.
New versions of ORPG's are also commonly downloaded on sites, instead of on b.net, simply for the wait-time.
In my opinion, ORPG's are the only maps that can have a filesize that reaches the limit (4MB, with other words).
With exception of some brand new maps, like Roller Coaster (this can be played over and over again, it's not really the normal type of map) and highly advanced maps (there are only a few of those, unless you are an amazing mapper with a really good team, don't consider your map highly advanced
).
Other Maps:
When you are creating other maps, it really depends: How many features does it have? Is it worth the wait? For example, if you are creating a small TD that lasts about half an hour, keep it below 1MB, but if your TD has a lot of renovating features and a large gameplay, less than 3MB would be ideal.
An AoS map may have a large file-size, if it's not just a DotA-rip-off, otherwise there would be no point in waiting a long time, while there's a better map around b.net that contains the same (but improved) features.
This goes for most maps: don't make the file-size too high if it's just a rip-off of another map.
In general: keep it as low as possible (there are map optimizers to reduce the map size).
I know it's a lot to read, but I hope it helped... in some way ><
