WITH uniqueips(frst, lst, ip, visits) AS
(
    SELECT MIN(requested_on), MAX(requested_on), ip, count(*) AS visits
    FROM Page_Statistic p
    GROUP BY ip
),
tried_demo(ip, tried_demo) AS
(
    SELECT DISTINCT ip.ip, ISNULL(p.page_statistic_id - p.page_statistic_id + 1, 0)
    FROM uniqueips ip
    LEFT JOIN Page_Statistic p ON p.ip = ip.ip AND p.tag = 'demo'
)
SELECT
i.*, ip.frst AS first_appeared_on,
    ip.lst AS last_appeared_on,
    ip.visits,
    d.tried_demo
FROM Ip_Location_Info i
JOIN uniqueips ip ON ip.ip = i.ip
JOIN tried_demo d ON ip.ip = d.ip
ORDER BY ip.frst

ISNULL(p.page_statistic_id - p.page_statistic_id + 1, 0)

case when p.page_statistic_id is null then 0 else 1 end

tried_demo(ip, tried_demo) AS
(
    SELECT DISTINCT p.ip, cast(1 as bit)
    FROM Page_Statistic
    WHERE p.tag = 'demo'
)

SELECT
i.*, ip.frst AS first_appeared_on,
    ip.lst AS last_appeared_on,
    ip.visits,
    coalesce(d.tried_demo,0) as tried_demo
FROM Ip_Location_Info i
JOIN uniqueips ip ON ip.ip = i.ip
LEFT JOIN tried_demo d ON ip.ip = d.ip
ORDER BY ip.frst