JSON

• http://www.postgresql.org/docs/9.3/static/functions-json.html

CREATE TABLE raw_crashes (
  uuid uuid NOT NULL,
  raw_crash json NOT NULL,
  date_processed timestamp with time zone
);

-- (9.2 compatible code) 

CREATE TEMPORARY TABLE new_android_devices
AS
  SELECT DISTINCT
    json_object_field_text(raw_crash, 'Android_CPU_ABI') as android_cpu_abi
    , json_object_field_text(raw_crash, 'Android_Manufacturer') as android_manufacturer
    , json_object_field_text(raw_crash, 'Android_Model') as android_model
    , json_object_field_text(raw_crash, 'Android_Version') as android_version
  FROM raw_crashes
  WHERE
    date_processed >= updateday
    AND date_processed <= (updateday + 1)
  GROUP BY
    android_cpu_abi
    , android_manufacturer
    , android_model
    , android_version
;

Some syntax:

SELECT '{"key": "value"}'::json->'key';

 ?column? 
----------
 "value"
(1 row)

HLL

• http://tapoueh.org/blog/2013/02/25-postgresql-hyperloglog

CREATE TABLE daily_uniques (
  report_date            date UNIQUE,
  users           hll
);

UPDATE daily_uniques
   SET users = hll_add(users, hll_hash_text('123.123.123.123'))
 WHERE report_date = current_date;

• http://www.chesnok.com/daily/2013/11/12/how-i-write-queries-using-psql-ctes/

Creating a report that retrieves a count of same uptime_string in a selection of crash signatures for some period of time.

Using subqueries instead of CTEs:

SELECT category
    , report_count
    , round((report_count * 100::numeric)/total_count,3)::TEXT
as percentage
FROM
(
    SELECT
        category
        , report_count
        , sum(report_count) OVER () as total_count
        FROM
    (
            SELECT uptime_string AS category
                , sum(report_count) AS report_count
            FROM signature_summary_uptime
                JOIN signatures USING (signature_id)
            WHERE
                signatures.signature = 'Fake Signature #1'
                AND report_date >= '2013-08-05T00:00:00+00:00'::timestamptz
                AND report_date < '2013-08-12T00:00:00+00:00'::timestamptz
                AND product_name IN ('Firefox')  AND version_string IN ('1')
            GROUP BY category
        ) as crashes
) as totals
ORDER BY report_count DESC
;

Using CTES:

WITH crashes AS (                                                               
    SELECT uptime_string AS category                                                      
        , sum(report_count) AS report_count                                     
    FROM signature_summary_uptime                                               
        JOIN signatures USING (signature_id)                                       
    WHERE                                                                           
        signatures.signature = 'Fake Signature #1'                                             
        AND report_date >= '2013-08-05T00:00:00+00:00'::timestamptz             
        AND report_date < '2013-08-12T00:00:00+00:00'::timestamptz              
        AND product_name IN ('Firefox')  AND version_string IN ('1')            
    GROUP BY category                                                           
),                                                                              
totals AS (                                                                     
    SELECT                                                                      
        category                                                                
        , report_count                                                          
        , sum(report_count) OVER () as total_count                              
    FROM crashes                                                                
)                                                                               
SELECT category                                                                 
    , report_count                                                              
    , round((report_count * 100::numeric)/total_count,3)::TEXT                  
as percentage                                                                   
FROM totals                                                                     
ORDER BY report_count DESC                                                      
;

• http://www.postgresql.org/docs/9.3/static/tutorial-window.html
• http://www.postgresql.org/docs/9.3/static/functions-window.html

The following functions are also defined in addition to the standard aggregate functions like sum() and avg():

row_number() 
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(num_buckets integer) possible
lag(value any [, offset integer [, default any ]])
lead(value any [, offset integer [, default any ]])
first_value(value any)
last_value(value any)
nth_value(value any, nth integer)

Example of a simple moving average:

CREATE TABLE lag_log (
    replica_name text NOT NULL,
    moment timestamp with time zone NOT NULL,
    lag integer NOT NULL,
    master text NOT NULL
);

-- Populate the data
insert into lag_log ( SELECT 'test1', generate_series(now(), now()+'1 hour'::interval, '5 minutes'), generate_series(1, 12, 1), 'master'); 

select moment, lag, avg(lag) 
  OVER ( order by moment desc rows between 1 following and 12 following )  
  FROM lag_log;

• Socorro supports 9.2 and higher - We have a reporting database you can get access to
• Amazon RDS Postgres - https://aws.amazon.com/rds/postgresql/
• Heroku Postgres - https://www.heroku.com/postgres
• Engine Yard - https://www.engineyard.com/resources/database-support

Also, Vertica supports WITH queries and many key Postgres SQL features.