SELECT s1.scope, seasons.season_key as season, ss.sub_season_key,
	        dn_player.first_name, dn_player.last_name, positions.abbreviation,
	        p.person_key, p.id as player_id, dn_team.full_name as team_name, dn_team.abbreviation as team_abbreviation, t.team_key,
	    s.*

        FROM persons p
        JOIN stats s1 ON (s1.stat_holder_id = p.id AND s1.stat_repository_type = 'ice_hockey_offensive_stats')
        JOIN ice_hockey_offensive_stats s ON s1.stat_repository_id = s.id
        JOIN teams t ON s1.stat_membership_id = t.id
        JOIN sub_seasons ss ON s1.stat_coverage_id = ss.id
        JOIN seasons ON seasons.id = ss.season_id
        JOIN affiliations a ON (a.id = seasons.league_id AND a.affiliation_key = 'l.nhl.com')
        JOIN display_names dn_player ON s1.stat_holder_id = dn_player.entity_id
        JOIN display_names dn_team ON s1.stat_membership_id = dn_team.entity_id

        JOIN person_phases pp ON (p.id = pp.person_id)
		JOIN positions ON pp.regular_position_id = positions.id
		JOIN publishers AS pub ON (pub.id = t.publisher_id AND pub.publisher_key = 'stats.com')

		 WHERE s1.stat_holder_type = 'persons' AND s1.stat_coverage_type = 'sub_seasons' AND dn_player.entity_type = 'persons' AND dn_team.entity_type = 'teams' AND s1.scope IS NULL AND t.team_key = 'l.nhl.com-t.18'
		ORDER BY seasons.season_key DESC