Creating an analytic query (whether AI or SQL) is an iterative process of query, review and test, revise, and query again. Once a complex analytic is complete, saving it as a database view allows you and others a simplified path to replicating the same logic with a single SELECT.

Let's Get Started!

To be able to validate your script, we'd ask that you generate a new file called Using_API_Keys.sh that you can commit to your branch of the SXT-Community/SXTAccreditation repo at the end of the activity.

Creating a Standard View

The first step in saving logic to the database as a view, is generating that database logic!

Get Creative!

We'll provide you a query below, however using this logic is not a requirement - in fact, we'd recommend you take some time to explore the data, and see what kind of interesting analytics you can discover!

Create the DB Logic

Standard views are nothing more than a SQL statement saved to the Space and Time Database and given an object name, so that it can be queried as if it were a table. As an example, let's start with the SQL statement below (or substitute your own!):

/* AAVE v3: Flash Loan value of wETH or wstETH pool, rolling 90 days 
   runtime: ≈5sec */

SELECT cast(time_stamp as date) as Loan_Date
, case when substr(Asset,1,6)='0xc02a' then 'wETH' else 'wstETH' end as Asset_Name
, sum(cast(amount as decimal(38,2)))/1e18 as Amt_Total
from AAVE_V3_ETHEREUM.POOL_EVT_FLASHLOAN
where time_stamp between current_date-91 and current_Date-1
  and asset in( lower('0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0') --wstETH
               ,lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')) --wETH
group by cast(time_stamp as date), asset
order by 1

Like any database object, you will need a public key to assign the object when saving to the database, to allow the view owner (holder of the private key) to authorize activities like CREATE, DROP, and creating biscuits.

Create the Keypair

If you've completed the "Create Your Own Table" accreditation section, the rest of this section will be a repeat.

Combine Logic and Keypair

Let's put the query together with the keypair, and create the view!

CREATE VIEW SXTTemp.vw_Aave_wETH_wstETH_Pool_<My UserID>
with "public_key=51297647296..., access_type=public_read"
as
---
SELECT cast(time_stamp as date) as Loan_Date
, case when substr(Asset,1,6)='0xc02a' then 'wETH' else 'wstETH' end as Asset_Name
, sum(cast(amount as decimal(38,2)))/1e18 as Amt_Total
from AAVE_V3_ETHEREUM.POOL_EVT_FLASHLOAN
where time_stamp between current_date-91 and current_Date-1
  and asset in( lower('0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0') --wstETH
               ,lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')) --wETH
group by cast(time_stamp as date), asset
order by 1

The "with" command takes two arguments:

• public_key - the required hex formatted public key, to allow authorization.
• access_type - similar to tables, this specifies the default access allowed without a biscuit. We are setting public_read here, to allow anyone to run our analysis of this public dataset.
  Note that for standard views, the user must also have access to the underlying tables as well as the view.

Executing the above SQL in the method of your choice (SXTCLI, API, or Space and Time Studio will compile and save the view.

👍

Congrats! You've created your first standard view!

/* display 5 years of my own wallet TXN history 
   runtime: ≈4 seconds 
   probably a good candidate for a standard view... */
---
Select 
  substr(time_stamp, 1, 7) as YrMth
, sum(case 
      when from_address = my_Wallet
      then value_/1e18 end) as Amt_Out
, sum(case 
      when to_address = my_Wallet
      then value_/1e18 end) as Amt_In
, count(*) as Txn_Count
from ETHEREUM.TRANSACTIONS
join (select lower('0x123abd...') as my_Wallet)
  on from_address = my_Wallet
  or to_address   = my_Wallet
where time_stamp between current_date-(5*365) and current_date-1
Group by 1 order by 1
  

Materialized View

If your analytic takes a significant amount of time to run, you may want the view to execute and cache the results, so the materialized view runs instantly! The cache will refresh automatically on a timer, by default, once a day.

As an example, let's change our above example to a materialized view that runs not against 91 days, but against the entire history of Aave V3, going back to March of 2022. This still only runs in about 5-6 seconds, probably not worth materializing, but for this example:

CREATE MATERIALIZED VIEW SXTTemp.mvw_Aave_wETH_wstETH_Pool_<My UserID>
with "public_key=51297647296..., access_type=public_read, refresh_interval=1440"
as
---
SELECT cast(time_stamp as date) as Loan_Date
, case when substr(Asset,1,6)='0xc02a' then 'wETH' else 'wstETH' end as Asset_Name
, sum(cast(amount as decimal(38,2)))/1e18 as Amt_Total
from AAVE_V3_ETHEREUM.POOL_EVT_FLASHLOAN
where time_stamp between '2022-04-01' and current_Date-1
  and asset in( lower('0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0') --wstETH
               ,lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')) --wETH
group by cast(time_stamp as date), asset
order by 1

We only had to make 2 changes from above:

• First 3 words of the command are now CREATE MATERIALIZED VIEW instead of just CREATE VIEW.
• Second line, added refresh_interval to the with statement. The number (1440) is the number of minutes between data refreshes.

We also changed the name slightly, to begin with SXTTemp.mvw_ to indicate "materialized view", however that is not a requirement, simply a naming convention.

📘

Currently the maximum allowed refresh_interval is 1440, aka 24 hours. However, you can manually refresh at any time by calling the API: view-materialized-refresh-force

Executing the above SQL in the method of your choice (SXTCLI, API, or Space and Time Studio will compile and save the view.

👍

Congrats! You've created your first materialized view!

You may recall from the "Data and Queries" activity, that the Space and Time Studio will automatically create materialized views for you! There are some limitations, for instance it will always create the materialized view in one specific schema.

Building views yourself allows you to place the view where you want, and to assign the prefered access_type to restrict access, which allows you to enforce the use of biscuits.

Get Credit:

You'll get credit for this activity if you:

• Create a view
• Create a materialized view
• Be Creative! It might win you prizes (hint hint)
• Commit your work file to the SXT-Community/SXTAccreditation repo

{/ looking for successful Create View and Create materialized view /}