Amplitude SQL: Table schema and field shortcuts

Special field shortcuts

Amplitude SQL is built directly into the Amplitude chart experience, so you can use the same Amplitude interface, including the datepicker and chart saving experience. Amplitude SQL also provides special fields for shortcuts:

• $date: Applies the time range from the datepicker and updates the query over time. It refers to the event time on the event and respects the project's timezone. Otherwise, Amplitude SQL returns data in UTC. Use it with $events.
• $events: The shorthand for the table in your current project. This table automatically handles merged users. Use it with $date.
• $amplitude_id: The original Amplitude ID for the user. Use this field to automatically handle merged users.

Table schema

Amplitude SQL uses a one-table schema. The $events table handles the merged user mappings automatically. The merged users table is also available to view, so you can quickly check the number of users Amplitude merged into one.

The following tables show the schema of $events and merged users.

$events table

• $amplitude_id
  • Data type: NUMBER(38,0)
  • Description: The original Amplitude ID for the user. Use this field to automatically handle merged users.
• adid
  • Data type: VARCHAR(16777216)
  • Description: (Android) Google Play Services advertising ID (AdID). Amplitude usually wipes this after ingestion, so it's typically blank.
• amplitude_attribution_ids
  • Description: Anonymized hash of the advertising IDs Amplitude stores for internal purposes. This appears if advertising IDs were sent, which proves that adid/idfv existed even though they're currently wiped.
• amplitude_event_type
  • Data type: VARCHAR(16777216)
  • Description: Amplitude-specific identifiers based on events Amplitude generates. This is a legacy field, so event_type should suffice for all queries.
• amplitude_id
  • Data type: NUMBER(38,0)
  • Description: An internal ID Amplitude uses to count unique users.
• app
  • Data type: NUMBER(38,0)
  • Description: Project ID found in your project's Settings page.
• city
  • Data type: VARCHAR
  • Description: City.
• client_event_time
  • Data type: TIMESTAMP
  • Description: Local timestamp (UTC) of when the device logged the event.
• client_upload_time
  • Data type: TIMESTAMP
  • Description: The local timestamp (UTC) of when the device uploaded the event.
• country
  • Data type: VARCHAR
  • Description: Country.
• data
  • Data type: VARIANT
  • Description: Dictionary that stores fields like first_event and merged_amplitude_id.
• device_brand
  • Data type: VARCHAR(16777216)
  • Description: Device brand.
• device_carrier
  • Data type: VARCHAR(16777216)
  • Description: Device carrier.
• device_family
  • Data type: VARCHAR(16777216)
  • Description: Device family.
• device_id
  • Data type: VARCHAR(16777216)
  • Description: The device-specific identifier.
• device_manufacturer
  • Data type: VARCHAR(16777216)
  • Description: Device manufacturer.
• device_model
  • Data type: VARCHAR(16777216)
  • Description: The device model.
• device_type
  • Data type: VARCHAR(16777216)
  • Description: Device type.
• dma
  • Data type: VARCHAR(16777216)
  • Description: Designated marketing area (DMA).
• event_id
  • Data type: NUMBER(38,0)
  • Description: A counter that distinguishes events.
• event_time
  • Data type: TIMESTAMP
  • Description:
    • Amplitude timestamp (UTC), which is the client_event_time adjusted by the difference between server_received_time and client_upload_time. Specifically:
    • event_time = client_event_time + (server_received_time - client_upload_time)
    • Amplitude uses this timestamp to organize events on charts.
    • If the difference between server_received_time and client_upload_time is less than 60 seconds, Amplitude doesn't adjust event_time, and it equals client_event_time.
• event_type
  • Data type: VARCHAR(16777216)
  • Description: The assigned type of event.
• followed_an_identify
  • Data type: BOOLEAN
  • Description: True if there was an identify event between this current SDK event and the last SDK event seen.
• groups
  • Data type: VARIANT
  • Description: Group types. Refer to the Accounts documentation for more information.
• idfa
  • Data type: VARCHAR(16777216)
  • Description: (iOS) Identifier for Advertiser. Amplitude usually wipes this after ingestion, so it's typically blank.
• ip_address
  • Data type: VARCHAR(16777216)
  • Description: IP address.
• location_lat
  • Data type: FLOAT
  • Description: Latitude.
• location_lng
  • Data type: FLOAT
  • Description: Longitude.
• os_name
  • Data type: VARCHAR(16777216)
  • Description: OS name.
• os_version
  • Data type: VARCHAR(16777216)
  • Description: OS version.
• paying
  • Data type: VARCHAR
  • Description: True if the user has ever logged any revenue, otherwise '(none)'. You can modify the property value through the Identify API.
• region
  • Data type: VARCHAR
  • Description: Region.
• server_upload_time
  • Data type: TIMESTAMP
  • Description: Amplitude timestamp (UTC) of when Amplitude servers received the event.
• session_id
  • Data type: NUMBER(38,0)
  • Description: The session start time in milliseconds since epoch.
• start_version
  • Data type: VARCHAR
  • Description: App version Amplitude first tracked the user on.
• user_id
  • Data type: VARCHAR(16777216)
  • Description: A readable ID you specify.
• uuid
  • Data type: VARCHAR(16777216)
  • Description: A unique identifier per row (event sent).
• version_name
  • Data type: VARCHAR(16777216)
  • Description: The app version.

Merged users table

To learn how Amplitude tracks unique users, refer to Track unique users.