/* ----------------------------------------------------------------------- */
/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 * @file path.sql_in
 *
 * @brief SQL functions for pathing functions
 * @date Sep 2015
 *
 */
/* ----------------------------------------------------------------------- */

DROP TABLE IF EXISTS "Weblog", "Path_output", "Path_output_tuples" CASCADE;
CREATE TABLE "Weblog" (event_timestamp TIMESTAMP,
            user_id INT,
            age_group INT,
            income_group INT,
            gender TEXT,
            region TEXT,
            household_size INT,
            "Click_event" INT,
            purchase_event INT,
            revenue FLOAT,
            "Margin" FLOAT);

INSERT INTO "Weblog" VALUES
(to_timestamp('04/14/2012 23:43:00', 'MM/DD/YYYY HH24:MI:SS'), 102201, 3, 3, 'Female', 'East', 3, 1, 1, 112, 36),
(to_timestamp('04/14/2012 23:56:00', 'MM/DD/YYYY HH24:MI:SS'), 101881, 2, 4, 'Male', 'West', 5, 0, 0, 0, 0),
(to_timestamp('04/15/2012 01:04:00', 'MM/DD/YYYY HH24:MI:SS'), 100821, 1, 4, 'Unknown', 'West', 3, 0, 0, 0, 0),
(to_timestamp('04/16/2012 23:57:00', 'MM/DD/YYYY HH24:MI:SS'), 101331, 2, 4, 'Female', 'East', 5, 1, 1, 456, 77);

/*
SELECT * FROM weblog ORDER BY event_timestamp ASC;
*/
SELECT path(
     '"Weblog"',              -- Name of the table
     '"Path_output"',         -- Table name to store the path results
     'user_id, age_group > 1, income_group > 1',             -- Partition expression to group the data table
     'event_timestamp ASC',         -- Order expression to sort the tuples of the data table
     'I:="Click_event"=0 AND purchase_event=0, Click:="Click_event"=1 AND purchase_event=0, Conv:=purchase_event=1',    -- Definition of various symbols used in the pattern definition
     'I(click){1}(CONV){1}',        -- Definition of the path pattern to search for
     'COUNT(*)'             -- Aggregate/window functions to be applied on the matched paths
    ,TRUE
     );