photo.sql

select
      count(case when photos.created_at < trips.start_date then distinct(photos.id) end) as before,
      count(case when photos.created_at >= trips.start_date and photos.created_at < trips.end_date then distinct(photos.id) end) as during,
      count(case when photos.created_at >= trips.end_date then distinct(photos.id) end) as after
       from trips
        inner join trip_segments
          on trip_segments.trip_id = trips.id
          inner join trip_segment_photos
            on trip_segment_photos.trip_segment_id = trip_segments.id
            inner join photos
              on photos.id = trip_segment_photos.photo_id