We used fresh pumpkin when we were in Illinois, and had a source of Dickinson pumpkins, which are the best for pies, etc.
This is a small, oblong pumpkin, seldom over 20 pounds, that looks more like a squash.
Here in western Canada, people look for the large, orange-skinned fruits which are suitable for jack-o-lanterns but lack the rich flavor of the Dickinsons. We make do with the canned Dickinson, put up by E. D. Smith, and Libby (brands like Stokley not found or uncommon in stores here).