Aggressive geometry has to do with many variables (including youthful bravado/ego), most of which have to do with the angles at which tubes are joined and the length of tubes. Very generally, steeper (more vertical) angles for your seat and head tube coupled to fork rake (go look it up if you wish) along with a shorter wheel base, which includes shorter chain stays and often less rake which brings your rear wheel up under you and pulls back your front wheel a bit which then means your bike steers quicker (another synonym might be the negatively connotated "twitchy") and responds with more instantaneous acceleration when you put your foot down (though hopefully you push down with both feet but not simultaneously). Aggressive geometry might also include a longer top tube to stretch you out aero fashion (make that "error" fashion if you are a chiropracter). The steeper head tube will also stretch you out because it will push your bars farther in front which can then be further increased if you you have not much stem showing (chiropracters personally favor higher stems, but for the sake of their practice they may want you on a very low stem).

Pilots, Sequoias and such are not aggressive geometry. Consider them "sensible" geometry. Thought "boring" by some, but then how boring is a body petrified and convoluted by an extreme biking position.

Seriously-- aggressive bikes can be lotsa fun to ride-- especially in a group. They accelerate, swoop and swerve, and respond to the slightest steering input from your butt down through the saddle. But, then again, they also send lotsa input back up to your butt and hands from any bumps or buzz in the road.

But, all I really know about is steel....the new wunder-materials may have their own variables.