It's a bit of a personal choice, but I like to use >= and <= and start and end of the bins to select.
E.g., I think this example query is correct (although, it's more efficient to use the materialized table).
SELECT
ROUND(SUM(if(lcp.end <= 2500, lcp.density, 0)), 4) AS good_lcp,
ROUND(SUM(if(lcp.start >= 2500 and lcp.end <= 4000, lcp.density, 0)), 4) AS ni_lcp,
ROUND(SUM(if(lcp.start >= 4000, lcp.density, 0)), 4) AS poor_lcp
FROM
`chrome-ux-report.all.202206`,
UNNEST(largest_contentful_paint.histogram.bin) AS lcp
WHERE