--
-- phpWebLogAnalyzer - powerful weblog and analyzer
--
-- sql/queries/pgsql.sql - PostgreSQL statistics queries
-- ____________________________________________________________
--
-- Developed by Ondrej Jombik <nepto@platon.sk>
-- Copyright (c) 2001-2005 Platon SDG, http://platon.sk/
-- All rights reserved.
--
-- See README file for more information about this software.
-- See COPYING file for license information.
--
-- Download the latest version from
-- http://platon.sk/projects/phpWebLogAnalyzer/
--

-- $Platon: phpWebLogAnalyzer/sql/queries/pgsql.sql,v 1.4 2003/01/27 01:07:57 nepto Exp $

-- accesses statistic for particular months

select date_trunc('month', TIMESTAMP) as month, count(distinct session_id) as
sessions, count(id)as accesses from weblog_accesses group by month order by
month;

-- ?

select date_trunc('month', a1.TIMESTAMP) as month1, date_trunc('day',
TIMESTAMP) as day 1, count(distinct session_id) as sessions1, count(id) as
accesses1 from weblog_accesses as a1 group by month1, day1 having
date_trunc('day', TIMESTAMP) = (select date_trunc('day', TIMESTAMP) as day2
from weblog_accesses as a2 where date_trunc('month', a2.TIMESTAMP) = month1
group by day2 having count(distinct session_id) = 15 ) order by day;

-- ?!

select date_trunc('day', TIMESTAMP) as day from weblog_accesses group by day
having count(distinct session_id) = (select max(count(distinct session_id))
from weblog_accesses group by date_trunc('day', TIMESTAMP));