https://supabase.com/ logo
Join Discord
Powered by
Hi everyone - I am super new to interacting with d...
# sql
f
Hi everyone - I am super new to interacting with databases and using SQL/postgres. Could someone help me put together a query for this and help me understand some of what its doing? I have a table and the table has the columns [id, timestamp, name, price, volume]. Every hour, new data populates into the table with updated price/volume for a given name. This is how I want the table to display on the front end in a table:
Copy code
html
<tr>
    <th>Collection Name</th>
    <th>Current Floor Price</th>
    <th>Previous Floor Price</th>
    <th>Volume</th>
</tr>
So, I need to group by collection name and then order by timestamp and get the two most recent prices to put into the table (current and previous). What would the SQL for something like this look like? Hopefully I worded this properly enough to portray what I am trying to accomplish!
Just for additional context, this is what I currently have as a basic supabase query
Copy code
javascript
   const test = async () => {
    let { data, error } = await supabase
      .from("SolanaFloorTracker")
      .select("*")
      .order("CollectionName", { ascending: true })
      .order("created_at", { ascending: false });
    if (error) {
      console.error(error);
    }
    console.log(data);
   };
however, it returns data in a rather inefficient way that I would need to use a lot JavaScript and processing time to sort through the arrays and get what I need out of it which wouldn't be good in the long run when lots more data comes in.
t
you could create a view that does the aggregate
something like
Copy code
sql
create view SolanaFloorTrackers AS 
select psft.id, psft."timestamp", psft.name, psft.price, psft.volume
FROM (
  SELECT *, rank() over (partition by (sft.name) order by sft."timestamp" desc) as rank
from SolanaFloorTracker sft
) as psft
where psft.rank in (1,2)
edit: need to alias the inner query.
rank is a window function.
I realise that I haven't answered your question. And I feel you could use 
lag
instead of 
rank
here as one of the other postgresql Window Function available to you. Though after a quick look I couldn't get my head around how it would work. I'll try another way using rank.
Copy code
sql
create view SolanaFloorTrackers AS 
-- this is a CTE
with psft as (
  SELECT *, rank() over (partition by (sft.name) order by sft."timestamp" desc),
select 
c.id , c."timestamp", c.name, c.price, c.volume, p.price, p.volume
from psft c. -- c for current
left outer join psft p ON c.name = p.name. -- p for previous
where c.rank = 1 and p.rank =2;
f
I am not sure exactly what I am looking at here šŸ˜…
t
You can use the sql editor to deploy your own views or rpc Note that I used the same name as you had in your table though made it plural by appending an ā€˜sā€™. This is just a convention. You can select directly from the view once you create the view much like you would a table though just add the ā€˜sā€™. Probably best to find a tutorial that deploys a view / rpc to supabase first and come back to this and it may make more sense.
f
Whats the difference between using a view versus rpc?
also what exactly is a view or rpc doing? like what does it mean
Sorry trying to learn!
and did you have a good tutorial for using a view? i found this on supabase channel

https://www.youtube.com/watch?v=MJZCCpCYEqkā–¾

but it is a function and only really shows using it within supabase ui rather than in code
t
I can't think how to explain views though the 
comedies
example in the [postgres docs](https://www.postgresql.org/docs/13/sql-createview.html) is a simplified example Its just a way to pre bake a query I guess to decrease complexity.
For rpc see a supabase docs example https://supabase.com/docs/guides/database/functions
PreviousNext
Powered by