2 most common are flooded cell lead acid (stock OEM) then AGM absorbed glass material (Optima, Odyssey and many others). Every big parts house chain has their own private labels mostly made by the few big manufacturers. Their is only so much magic you can put into a lead based battery, then it comes down to warranty, availability and mounting/shape. Optima has hands down been the go to shape and availability. For years you have been able to buy pre-made mounts and find drop in replacements at most all auto parts stores. Other brands have changed shape over the years and private labels come and go so battery mounts might need to be more universal and availability is a further drive or you have to switch brands. Warranty around 3 years is common and also when many batteries will start to die. Some have better warranties than others or prorate. Warranty is a form of insurance not so much an indication of life expectancy.
For life of batteries it really depends on how you take care of them and how much you use them. Optima is the most common performance battery so it is being used more than others and in non oem electrical systems living a hard life. More batteries out there = more claims of them going dead. Percentage might be on par will all the others. Combine that with a long history and you have more claims than anybody. The new brands also have a good 3 years to look good and have minimal failures and get their name out there as the best thing ever before age catches up with the brand and their reliability is just like all the others. The beloved Northstar Platinum from Sears lived this short life until Sears got so many warranty claims they discontinued the battery. Not the batteries fault, it just had too good of a warranty for the sale price and alot of poorly maintained or wired vehicles they went into.
Use of chargers and maintainers is a must for any battery that sits for an extended period of time or doesn't have time to recharge in the vehicle fully. Undercharged batteries and small drains are probably the biggest killer of batteries within our sport. My dual yellow tops are about 10 years old now and still good, but I maintain them. I had a yellow top die in my daily driver after about 4 years. About the same time I got a new daily driver and the factory OEM battery died at 2 years and I replaced it with a yellow top. In my wife's stock Grand Cherokee I just dropped in the third battery in a 8 year span. OEM AGM lasted 5 years and the O'reillys AGM lasted 3 years. I replaced it with a Napa AGM as they were the only ones who had one that would fit. Jeep decided to mount it under the passenger seat so it is a special shape that is hard to find so I get whatever I can find.
So decide whatever plastic Tupperware shape you want the battery holders to be and factor in the warranty and you are set.