const { apiKey } = props; // options based on https://flipsidecrypto.xyz/edit/queries/e1d765f3-2b01-4363-9e17-aea861260ee3 const addressOptions = [ "Sweat", "NEAR Crowd", "Aurora Faucet", "Spin", "Ref Finance", "Orderly", "PlayEmber", "Tonic", "Near Wrapper", "Tether (USDT)", "Circle (USDC)", "Paras", "Learn Near Club", "Custom", ]; State.init({ startDate: "2023-01-01", endDate: "2023-12-31", addresses: [], customAddresses: [], hasSqlError: false, isActivitiesSqlRunning: false, data: [], hasNewData: false, }); /** input functions */ const onAddressSelectValueChange = (value) => { State.update({ addresses: value, }); }; const onCustomAddressesValueChange = ({ target }) => { let customAddresses = target.value.split(","); State.update({ customAddresses, }); }; const onStartDateChange = ({ target }) => { State.update({ startDate: target.value, }); }; const onEndDateChange = ({ target }) => { State.update({ endDate: target.value, }); }; /** sql functions */ const buildSql = () => { let { addresses, customAddresses, startDate, endDate } = state; let shouldIncludeCustomAddresses = addresses.includes("Custom"); if (shouldIncludeCustomAddresses) { addresses = [...addresses, ...customAddresses]; } // remove "Custom" from address list and remove whitespaces addresses = addresses .filter((x) => x != "Custom" && x != "") .map((x) => x.trim().toLowerCase()); if (addresses.length == 0) { return ""; } let addressStr = "'" + addresses.join("','") + "'"; let sql = `with call_contracts as ( select block_timestamp::date as date, lower(trim(project_name)) as project_name, count(method_name) as call_contracts, count( DISTINCT(tx_hash) ) as transactions, count( DISTINCT(signer_id) ) as users from near.core.fact_actions_events_function_call a join near.core.dim_address_labels b on a.receiver_id = b.address where block_timestamp::date >= '${startDate}' and block_timestamp::date <= '${endDate}' and (lower(trim(project_name)) IN (${addressStr}) or lower(trim(b.address)) IN (${addressStr})) group by 1,2 ), volume as ( select block_timestamp::date as date, lower(trim(b.project_name)) as project_name, sum( deposit / pow(10, 24) ) as near_amount, avg( deposit / pow(10, 24) ) as avg_near_amount from NEAR.core.fact_transfers a join near.core.dim_address_labels b on a.tx_receiver = b.address where block_timestamp::date >= '${startDate}' and block_timestamp::date <= '${endDate}' and (lower(trim(project_name)) IN (${addressStr}) or lower(trim(b.address)) IN (${addressStr})) and STATUS = 'true' group by 1,2 ), gas_fee as ( select block_timestamp::date as date, lower(trim(b.project_name)) as project_name, sum( TRANSACTION_FEE / pow(10, 24) ) as gas_amount, avg( TRANSACTION_FEE / pow(10, 24) ) as avg_gas_amount from NEAR.core.fact_transactions a join near.core.dim_address_labels b on a.tx_receiver = b.address where block_timestamp::date >= '${startDate}' and block_timestamp::date <= '${endDate}' and (lower(trim(project_name)) IN (${addressStr}) or lower(trim(b.address)) IN (${addressStr})) group by 1,2 ), date_object as ( select distinct date_day as date, lower(trim(project_name)) as project_name from ethereum.core.dim_dates d join near.core.dim_address_labels l where date_day >= '${startDate}' and date_day <= '${endDate}' and (lower(trim(project_name)) IN (${addressStr}) or lower(trim(l.address)) IN (${addressStr})) ), agg as ( select coalesce(c.date, d.date) as date, coalesce(c.project_name, d.project_name) as project_name, users, transactions, call_contracts, near_amount, avg_near_amount, coalesce(gas_amount, 0) as gas_amount, coalesce(avg_gas_amount, 0) as avg_gas_amount from ( select coalesce(a.date, b.date) as date, coalesce(a.project_name, b.project_name) as project_name, coalesce(users, 0) as users, coalesce(transactions, 0) as transactions, coalesce(call_contracts, 0) as call_contracts, coalesce(near_amount, 0) as near_amount, coalesce(avg_near_amount, 0) as avg_near_amount from volume a full outer join call_contracts b on a.project_name = b.project_name and a.date = b.date ) c full outer join gas_fee d on c.project_name = d.project_name and c.date = d.date ), date_agg as ( select d.date, d.project_name, nvl(users, 0) as users, nvl(transactions, 0) as transactions, nvl(call_contracts, 0) as call_contracts, nvl(near_amount, 0) as near_amount, nvl(avg_near_amount, 0) as avg_near_amount, nvl(gas_amount, 0) as gas_amount, nvl(avg_gas_amount, 0) as avg_gas_amount from date_object d join agg a on a.date = d.date and a.project_name = d.project_name ) select *, sum(near_amount) over (partition by project_name order by date) as cum_near_amount, sum(transactions) over (partition by project_name order by date) as cum_transactions, sum(call_contracts) over (partition by project_name order by date) as cum_call_contracts from date_agg order by date, project_name `; return sql; }; const verifyAndRun = (runQuery /* callback */) => { State.update({ hasSqlError: false, }); let sql = buildSql(); if (!sql) { State.update({ hasSqlError: true, }); return; } State.update({ hasNewData: true, data: [], }); runQuery(sql); }; /** Main component */ const Component = ({ runQuery, data, hasError, isRunning, isLoading }) => { if (state.isActivitiesSqlRunning != isRunning) { State.update({ isActivitiesSqlRunning: isRunning, }); } if (isRunning) { return ( <button disabled className="btn btn-primary w-100"> <div className="d-flex flex-row justify-content-center align-items-center w-100"> <i className="spinner-grow spinner-grow-sm me-3"></i> <span>Getting Data...</span> </div> </button> ); } if (hasError) { return ( <div className="d-flex flex-column justify-content-center" style={{ height: "60vh" }} > <button onClick={() => { verifyAndRun(runQuery); }} > Try Again </button> <div className="h-100 d-flex align-items-center justify-content-center"> <strong className="text-danger">Error!</strong> </div> </div> ); } if (!data || data.length == 0) { return ( <div className="d-flex flex-column justify-content-center" style={{ height: "60vh" }} > <button onClick={() => { verifyAndRun(runQuery); }} > Search </button> <div className="h-100 d-flex align-items-center justify-content-center"> <strong>No data!</strong> </div> </div> ); } if (state.hasNewData) { State.update({ data, hasNewData: false, }); } return ( <div className="d-flex flex-column justify-content-center"> <button onClick={() => { verifyAndRun(runQuery); }} > Search </button> </div> ); }; /** Page Layout */ const Layout = () => { return ( <> <div className="row"> <div className="col-6 mb-3"> <div className="input-group"> <div className="input-group-prepend"> <span className="input-group-text"> <span>From</span> </span> </div> <input type="date" onChange={onStartDateChange} className="form-control" value={state.startDate} disabled={state.isActivitiesSqlRunning} /> </div> </div> <div className="col-6 mb-3"> <div className="input-group"> <div className="input-group-prepend"> <span className="input-group-text"> <span>To</span> </span> </div> <input type="date" onChange={onEndDateChange} className="form-control" value={state.endDate} disabled={state.isActivitiesSqlRunning} /> </div> </div> <div className="col-12 mb-3"> <div className="input-group"> <div className="input-group-prepend"> <span className="input-group-text"> <span>Addresses</span> </span> </div> <Typeahead options={addressOptions} multiple onChange={onAddressSelectValueChange} placeholder="Choose a project(s).." disabled={state.isActivitiesSqlRunning} /> </div> </div> <div className={`col-12 mb-3 ${ state.addresses.includes("Custom") ? "" : "d-none" }`} > <div className="d-flex flex-column"> <input type="text" className="form-control" onChange={onCustomAddressesValueChange} value={state.customAddresses.join(",")} placeholder="Separate addresses using a comma, eg. Project1,Project2" disabled={state.isActivitiesSqlRunning} /> </div> </div> </div> </> ); }; // "users": 0, //"transactions": 0, //"call_contracts": 0, //"near_amount": 20778.625208997, //"avg_near_amount": 164.909723881, //"gas_amount": 0.01069878758, //"avg_gas_amount": 0.0000849110125 // rerendering issues need to be solved const Graphs = () => { let { data } = state; let txByDate = {}; let totalTransactions = 0; let totalVolume = 0; let totalGas = 0; let totalCallContracts = 0; let maxTransaction = 0; let maxUser = 0; let maxContractCalls = 0; let maxVolume = 0; let maxAvgAmount = 0; let maxGas = 0; let maxAvgGasAmount = 0; data.forEach((x) => { totalTransactions += x.transactions; totalVolume += x.near_amount; totalGas += x.gas_amount; totalCallContracts += x.call_contracts; maxUser = maxUser < x.users ? x.users : maxUser; maxContractCalls = maxContractCalls < x.call_contracts ? x.call_contracts : maxContractCalls; maxVolume = maxVolume < x.near_amount ? x.near_amount : maxVolume; maxAvgAmount = maxAvgAmount < x.avg_near_amount ? x.avg_near_amount : maxAvgAmount; maxGas = maxGas < x.gas_amount ? x.gas_amount : maxGas; maxAvgGasAmount = maxAvgGasAmount < x.avg_gas_amount ? x.avg_gas_amount : maxAvgGasAmount; if (!txByDate[x.date]) { txByDate[x.date] = 0; } txByDate[x.date] += x.transactions; }); let txByDateArray = []; for (const [date, transactions] of Object.entries(txByDate)) { txByDateArray.push({ date, transactions }); maxTransaction = maxTransaction < transactions ? transactions : maxTransaction; } return ( <> {state.data.length > 0 && ( <> <div className="mt-5"></div> <div className="row mb-3"> <div className="col-4 p-2"> <div className="card shadow p-3 d-flex flex-column align-items-center justify-content-center"> <span>Total Transactions</span> <strong style={{ fontSize: 25 }}> {totalTransactions.toLocaleString("en", { maximumFractionDigits: 0, minimumFractionDigits: 0, })} </strong> </div> </div> <div className="col-4 p-2"> <div className="card shadow p-3 d-flex flex-column align-items-center justify-content-center"> <span>Total Near Volume (NEAR)</span> <strong style={{ fontSize: 25 }}> {totalVolume.toLocaleString("en", { maximumFractionDigits: 2, minimumFractionDigits: 2, })} </strong> </div> </div> <div className="col-4 p-2"> <div className="card shadow p-3 d-flex flex-column align-items-center justify-content-center"> <span>Total Gas (NEAR)</span> <strong style={{ fontSize: 25 }}> {totalGas.toLocaleString("en", { maximumFractionDigits: 2, minimumFractionDigits: 2, })} </strong> </div> </div> </div> {/** Calendar Chart */} <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-2" props={{ dateColumn: "date", dataColumn: "transactions", data: txByDateArray, legendMax: maxTransaction, legendMin: 0, label: "Count", title: "Number of Transactions by Date", }} /> {/** Line Chart */} <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "users", data, legendMax: maxUser, legendMin: 0, label: "Count", strokeColumn: "project_name", isXDate: true, title: "Number of Unique Wallets by Date", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "call_contracts", data, legendMax: maxContractCalls, legendMin: 0, label: "Count", strokeColumn: "project_name", isXDate: true, title: "Number of Contract Calls by Date", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "near_amount", data, legendMax: maxVolume, legendMin: 0, label: "Volume", strokeColumn: "project_name", isXDate: true, title: "NEAR Volume by Date", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "avg_near_amount", data, legendMax: maxAvgAmount, legendMin: 0, label: "Volume", strokeColumn: "project_name", isXDate: true, title: "Average Near Volume by Date", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "gas_amount", data, legendMax: maxGas, legendMin: 0, label: "Gas Used", strokeColumn: "project_name", isXDate: true, title: "Gas Used by Date", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-3" props={{ xColumn: "date", yColumn: "avg_gas_amount", data, legendMax: maxAvgGasAmount, legendMin: 0, label: "Gas Used", strokeColumn: "project_name", isXDate: true, title: "Average Gas Used by Date", }} /> {/** Bar Chart */} {/** * <Widget src="kida.near/widget/Untitled-4" props={{ xColumn: "date", yColumn: "users", data, legendMax: data .map((x) => x.users) .reduce((a, b) => (a > b ? a : b)), legendMin: 0, label: "Number of Unique Wallets", strokeColumn: "project_name", isXDate: true, }} /> */} {/** Area Chart */} <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-5" props={{ xColumn: "date", yColumn: "cum_near_amount", data, legendMax: totalVolume, legendMin: 0, label: "Volume", strokeColumn: "project_name", isXDate: true, title: "Cumulative Volume (NEAR)", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-5" props={{ xColumn: "date", yColumn: "cum_transactions", data, legendMax: totalTransactions, legendMin: 0, label: "Count", strokeColumn: "project_name", isXDate: true, title: "Cumulative Number of Transactions", }} /> <div className="mt-5"></div> <Widget src="kida.near/widget/Untitled-5" props={{ xColumn: "date", yColumn: "cum_call_contracts", data, legendMax: totalCallContracts, legendMin: 0, label: "Count", strokeColumn: "project_name", isXDate: true, title: "Cumulative Contract Calls", }} /> </> )} </> ); }; return ( <div className="p-2"> {/* <iframe className="w-100" style={{ height: "300px" }} srcDoc={content} /> */} <Layout /> {/** Flipside Data Wrapper */} <Widget src="kida.near/widget/Untitled-0" props={{ apiKey: "f1f1b6f1-1bdf-4d19-8c9c-a59dc1642c8b", component: Component, }} /> <Graphs /> {state.hasSqlError && ( <strong className="text-danger">Invalid Parameters!</strong> )} </div> );