Spring Heeled Jack

Cask Keg Can

SELECT MIN(brewery_name) AS brewery_name, beer_name, '/real-ale-brewery/' || MIN(BW.url) || '/' || B.url || COALESCE(BCT.url, '') AS url, abv, B.beer_container_type_id, '/images/beers/' || I.image_url AS beer_image, coalesce(colour, '#F0ED68') AS colour, beer_container_type, COALESCE(rating_count, 0) AS rating_count, COALESCE(total_rated, 0) AS total_rated, beer_type FROM ( SELECT B.beer_uuid, beer_name, BC.beer_container_id, beer_container_type_id, url, abv, beer_type_id FROM beer B INNER JOIN beer_container BC ON BC.beer_uuid = B.beer_uuid AND beer_container_type_id = 1 LEFT JOIN ( SELECT COUNT(user_venue_id) AS popular_beer, beer_container_id FROM user_venue GROUP BY beer_container_id ) PB ON PB.beer_container_id = BC.beer_container_id WHERE B.beer_group_id = 3 AND beer_type_id = 95 AND B.deleted = false AND B.active = true AND B.beer_uuid <> '{873FB0D9-8FA5-4974-BF62-2DC46039C5C6}' ORDER BY COALESCE(popular_beer, 0) DESC LIMIT 12 ) B INNER JOIN beer_brewery BB ON BB.beer_uuid = B.beer_uuid INNER JOIN brewery BW ON BW.brewery_uuid = BB.brewery_uuid INNER JOIN beer_container_type BCT ON BCT.beer_container_type_id = B.beer_container_type_id LEFT JOIN image I ON I.beer_container_id = B.beer_container_id AND image_sequence = 1 LEFT JOIN beer_type BT ON BT.beer_type_id = B.beer_type_id LEFT JOIN (SELECT beer_container_id, COUNT(rating_id) AS rating_count, SUM(rating) AS total_rated FROM user_rating GROUP BY beer_container_id) R ON R.beer_container_id = B.beer_container_id GROUP BY beer_name, b.url, bct.url, abv, b.beer_container_type_id, image_url, colour, beer_container_type, rating_count, total_rated, beer_type

St Austell Brewery

Anthracite

Black IPA 

4.3%

BrewDog

Arcade Nation

Black IPA 

5.2%

Four Thieves

Battersea Power

Black IPA 

4.7%

Golden Triangle Brewery

Black Hops

Black IPA 

4.6%

Three Blind Mice Brewery

Black IPA

Black IPA 

5.4%

Humpty Dumpty Brewery

Black Mill IPA

Black IPA 

5%

Humpty Dumpty Brewery

Branch Road Roller Coaster

Black IPA 

4.3%

Chapel Brewery

Dark Lord

Black IPA 

5%

BrewDog

LIVE Black Hammer

Black IPA 

7.2%

BrewDog

Obsidian Obvlivion

Black IPA 

6%

Conwy Brewery

Riptide

Black IPA 

4.3%

St Austell Brewery

Treeny's Black Ale

Black IPA 

4.5%