Any SQL report can return at most 100.000 results. If you don't add a LIMIT ... as the last part of your SQL query — or LIMIT is more than 100.000 — we automatically add LIMIT 100000 to the end of your SQL query.
There are two tables: users and events. The users table contains all static user properties, such as first visit, referring traffic source, etc and are prefixed with u_col_. The events table contains a log of all actions that users made, such as a purchase, pageview, etc and all properties are prefixed with e_col_.
To insert user or event column names, use the Add SQL menu option.
SELECT e_col_country FROM events WHERE id ='abcd_1234'
SELECT u_col_initial_country FROM users WHERE id ='user_xyz'
SQL results can be automatically be mapped to a chart by specific column names, using the .. AS .. keyword.
This can then be used to run for more user friendly displaying, instead of just a table, and to be able to run plugins on the data.
To make a chart, we need a minimum of one value and a timestamp. A value can be specified by giving a numerical column the name linevalX or barvalX, where X is the value number starting with 1. By adding a whitespace and then a custom display name, that will be used as display name.
Each value can be part of a specific date range, segment. This can be specific be adding the a linevalX and comma, and then and or both the keywords segX and/or dateX. Each of those can also have a space and their custom segment/date range display name.
To indicate time, return a column with timestamp type, as as name time_day, where day could also be hour, week, month or total.
As last, an additional groupX column name can be specified to indicate a grouping. Again a whitespace and custom display name can be used here.
SELECTmax(date_trunc('week', FROM_UNIXTIME(events.created_at)))as"time_week event creation week",COUNT(events.id)as"lineval1 Value 1,seg1 Segment A, date1 Custom date range", date_format(FROM_UNIXTIME(events.created_at),'%W')AS"group1 Day of week"FROM events GROUPBY date_trunc('week', FROM_UNIXTIME(events.created_at)), date_format(FROM_UNIXTIME(events.created_at),'%W')