[Impact Metric Submission]: Combined Daily and Monthly Project Activity

[nutkung1]

Combined Daily and Monthly Project Activity

Tags
Optimism Superchain
Project Activity
L1/L2 Gas Usage
Forks (Monthly)

Brief description

Describe how your impact metric is calculated in 2-3 sentences: This metric provides a combined view of daily project activity on the Optimism Superchain. It includes:

Daily L1 and L2 Gas Used: Indicates the on-chain activity level for a project's smart contracts.
Total Daily Events: Captures the overall number of events associated with a project (might include additional events beyond gas usage).
Monthly Fork Count (fetched from project_monthly_events): Shows the number of times a project's codebase was forked in a given month, potentially indicating active development or a collaborative nature.

SQL code block

Insert the SQL that we should run to replicate your impact metric:

WITH project_events AS (
  SELECT
    pe.project_id,
    DATE(pe.bucket_day) AS event_date,
    SUM(CASE WHEN pe.event_type = 'CONTRACT_INVOCATION_DAILY_L1_GAS_USED' THEN amount ELSE 0 END) AS daily_l1_gas_used,
    SUM(CASE WHEN pe.event_type = 'CONTRACT_INVOCATION_DAILY_L2_GAS_USED' THEN amount ELSE 0 END) AS daily_l2_gas_used,
    COUNT(*) AS total_events
  FROM `opensource-observer.oso.events_daily_from_project` pe
  GROUP BY pe.project_id, DATE(pe.bucket_day)
),
project_monthly_events AS (
  SELECT
    project_id,
    bucket_month,
    COUNT(*) AS monthly_events
  FROM `opensource-observer.oso.events_monthly_to_project`
  GROUP BY project_id, bucket_month
)
SELECT
  pe.project_id,
  pe.event_date,
  pe.daily_l1_gas_used,
  pe.daily_l2_gas_used,
  pe.total_events,
  COALESCE(pme.monthly_events, 0) AS monthly_events
FROM project_events pe
LEFT JOIN project_monthly_events pme
  ON pe.project_id = pme.project_id
  AND DATE(pe.event_date) = DATE(pme.bucket_month) - INTERVAL 1 DAY
ORDER BY pe.project_id, pe.event_date;

Optional: link

Add a link to script/notebook or longer form write-up about the metric: