Postgres INNER JOIN same table failing on CartoDB
The problem may be that you have multiple columns of the same name in the result (2x cp). When using SELECT * you would return both.

The simple fix for the query at hand would be USING in the join clause, which only keeps one instance of the joined column in the result (and seems smarter in this particular case anyway):

SELECT *
FROM   traffic_counts s1
JOIN  (SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2 USING (cp);


Updated SQL Fiddle.

BTW, SQL Fiddle itself was updated only recently to allow such ambiguity. Until some months ago it also raised an error for queries with duplicate names in the result. Quite a few clients cannot cope with that.