They are so uncommon let's take a second to appreciate SNES games with functional loading screens.
Game: Civilization 1
Loading time: ~40 seconds
Purpose: When you create a new game. It is procedurally generating the terrain of the map, placing the other civilizations you're playing with. Rather than a fixed map, it gets randomly generated each time plus the algorithm is customizeable to include different kinds of climates and features. The longest load time I have seen on this platform.
A video: https://youtu.be/oWtVe2qm7_w?t=129 (not mine, random search off youtube)
Game: Romance of the Three Kingdoms IV: Wall of Fire
Loading time: 2 or 3 seconds
Purpose: When you create a new game. Procedurally generating what commander has what resource allocations, which officers are where. Although the game comes with a fixed set of "scenarios", you can create your own commander and/or officers and choose who to control. I believe this, plus the difficulty level affects where the game places things and there are too many combinations to pre-compute them.
Game: Another World (also called, Out of this World)
Loading time: ~10 seconds
Purpose: Transitioning out of a simple menu, into the game
This game is, in a word, ambitious. Big, lush backgrounds with lots of things animating. Nothing looks like 'sprites'; they look like 2D vector graphics rasterized to low resolution to be honest.
This game was originally for Amiga and ported to SNES. The graphics have a unique style which unfortunately doesn't fit well to SNES technical constraints, which tend to involve either conventional 2D graphics modes with heavily re-used sprites, or just Mode 7-- neither of which fit this game well. Now that's not to say it can't work. Take, for example, the backgrounds of Super Mario RPG or Wonder Project J. You can make smart judgments about when to re-use sprites and try to hide them among the other elements. Of course, those games were designed from the ground-up for SNES. For this game, conversion to sprites would be an after-thought with the port. The sheer amount of graphics this game has is very large and scenes are organized in ways that are hard to break down into patterned elements.
While I don't tend to like this type of game- the latency of controls is so slow and loose, for one-- I respect its commitment to the unique art style. Given everything this game has going on, the loading screen is not frivolous.
Game: Sim City
Loading time: ~12 seconds
Purpose: When previewing the terrain on which to build your city- there are 1000 terrains (e.g., random seeds). Note that the load time is NOT just for creating a game with the level- it's to let you view a small 120x100 image. This, plus the instantaneous "OK" button tells us two things. First, there was not enough space on the cart to store 1000 of these images. Second, unpacking the preview image is about the same as unpacking the full map. While I think all of this is okay, they could have done with fewer better-optimized seeds. Fortunately the instruction manual has a couple pages of previews of maps which you can flip through quickly.
Game: Batman Forever
Loading time: ~5 seconds
Purpose: Transitioning out of cutscene into gameplay. Likely to be graphics-related. There are big, detailed sprites with lots of frames of animation.
Although there's an explanation for a load screen, it may not have been completely necessary. At 24Mbit, the cart is not that small; it's very likely the graphics could fit without super aggressive compression schemes. Some contributing factors to the need for load time may have been 1) the fact that this game is a port, and there wasn't time to optimize for any particular platform, and 2) these flashy 3D wireframe-map montage scenes, which would require different types of data and loader code.
Although this game gets a bad rap I respect its live-action-to-low-res-low-color Mortal Kombat aesthetic.
Maybe others I haven't encountered yet.
See, a couple big things affecting our modern conception of loading screens are optical media and network latency's failure to keep up with increasing size of game payloads. Computationally, modern computers have advanced a lot to the point where it is rare to see games spinning on procedural content like this, but it is common to spend a lot of time copying game assets from an optical medium to faster local solid-state storage, or downloading game assets from the internet.
There have been some modern efforts to curb load times. For example the Nintendo Switch had a return to a faster-than-optical-disk game media. You know, a cartridge. However, many Nintendo Switch games- non-procedural, fixed-level action games do have loading screens- screens which would have been unacceptable in 1995 but are acceptable now since we are used to them.