SELECT "year", GBIF_ISEA3HCode( 6, decimalLatitude, decimalLongitude, COALESCE(coordinateUncertaintyInMeters, 1000) ) AS isea3hCellCode, familyKey, family, speciesKey, species, basisOfRecord, COUNT(*) AS occurrences, MIN(COALESCE(coordinateUncertaintyInMeters, 1000)) AS minCoordinateUncertaintyInMeters, MIN(GBIF_TemporalUncertainty(eventDate)) AS minTemporalUncertainty, IF(ISNULL(familyKey), NULL, SUM(COUNT(*)) OVER (PARTITION BY familyKey, "year", basisOfRecord)) AS familyCount FROM occurrence WHERE occurrenceStatus = 'PRESENT' AND continent IS NOT NULL AND (coordinateUncertaintyInMeters <= 50000 OR coordinateUncertaintyInMeters IS NULL) AND kingdomKey = 6 AND hasCoordinate = TRUE AND speciesKey IS NOT NULL AND NOT ARRAY_CONTAINS(issue, 'ZERO_COORDINATE') AND NOT ARRAY_CONTAINS(issue, 'COORDINATE_OUT_OF_RANGE') AND NOT ARRAY_CONTAINS(issue, 'COORDINATE_INVALID') AND NOT ARRAY_CONTAINS(issue, 'COUNTRY_COORDINATE_MISMATCH') AND "year" IS NOT NULL GROUP BY "year", isea3hCellCode, familyKey, family, speciesKey, species, basisOfRecord ORDER BY "year" DESC, familyKey ASC, speciesKey ASC, basisOfRecord ASC;