NETWORKS

Mobile network reference data.

Data Freshness

Updated periodically.

Columns

Column	Type	Description
MCC	TEXT	Mobile Country Code.
MNC	TEXT	Mobile Network Code.
TADIG	TEXT	TADIG code for roaming.
ALPHA3	TEXT	ISO 3166-1 alpha-3 country code.
ALPHA2	TEXT	ISO 3166-1 alpha-2 country code.
COUNTRYNAME	TEXT	Country name.
ORGNAME	TEXT	Network operator or carrier name.
SRCFILE	TEXT	Reference code for the network data source.

Common Queries

Use NETWORKS to translate mobile country and network codes into country and operator information. Country lookups should generally filter COUNTRYNAME; network operator lookups should generally filter ORGNAME.

Find mobile network codes for a country:

SELECT
  MCC,
  MNC,
  ORGNAME
FROM NETWORKS
WHERE COUNTRYNAME = 'Japan'
ORDER BY MCC, MNC;

Join paths:

• SIM_STATS.VPLMN can join to NETWORKS by concatenating MCC || MNC.
• SIM_SESSION_EVENTS.CELL:mcc and SIM_SESSION_EVENTS.CELL:mnc can join to NETWORKS.
• SIM_SNAPSHOTS.SESSION_STATUS:cell:mcc and SIM_SNAPSHOTS.SESSION_STATUS:cell:mnc can join to NETWORKS.

SIM counts by country and plan for SIMs with recent traffic. Require SUBSCRIPTION IS NOT NULL to exclude subtotal or grouping-set rows:

WITH recent AS (
  SELECT
    st.SIM_ID,
    st.VPLMN,
    ANY_VALUE(ss.SUBSCRIPTION) AS subscription,
    SUM(st.DOWNLINK_BYTES + st.UPLINK_BYTES) AS total_bytes
  FROM SIM_STATS st
  LEFT JOIN SIM_SNAPSHOTS ss
    ON st.SIM_ID = ss.SIM_ID
  WHERE st.TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ)
  GROUP BY st.SIM_ID, st.VPLMN
),
active AS (
  SELECT
    SIM_ID,
    VPLMN,
    subscription
  FROM recent
  WHERE total_bytes > 0
),
with_country AS (
  SELECT
    a.SIM_ID,
    a.subscription,
    n.COUNTRYNAME AS country
  FROM active a
  LEFT JOIN NETWORKS n
    ON a.VPLMN = n.MCC || n.MNC
  WHERE a.subscription IS NOT NULL
)
SELECT
  country,
  subscription,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM with_country
WHERE country IS NOT NULL
GROUP BY country, subscription
ORDER BY country, subscription;