Advanced example

See how you can use Jetspike to enable complex SQL reporting on Firestore and solve your business' problems

Docs

Best way to learn a technology is to use it. In our advanced example, we build a complex report joining records from multiple collections and performing data aggregation. We will show how to solve challenging problems in a way you can encounter in your own applications.

Data

We have a database containing data about crypto investments of our users. The users make investments into cryptocurrencies and into NFTs. Our task is to create a report showing profits or losses of individual users.

DISCLAIMER: This tutorial and all information in it is intended for educational purposes only. It shall not be considered a source of financial information. The prices of individual cryptocurrencies in this tutorial do not represent real prices of respective cryptocurrencies. Jetspike Digital s.r.o. is not liable for the way this information is interpreted and utilized.

Collection investments contains records of all investments into cryptocurrencies. The type of coin and amount of coin is specified as well as the price at the time when the user bought it. Collection NFT contains information about individual NFT tokens, who owns them, how much they paid when they bought them and how much are they worth now. Collection currencies contains current prices of individual coins. Collection users contains names of our users.

Collection investments

Collection currencies

Collection NFT

Collection users

Our database can be visualized with following entity-relationship diagram:

Calculate current value of investments

To calculate the current value of investments, we first need to look into the price of the respective cryptocurrency and multiply it by the amount of it owned by the user. We use the JOIN statement to find establish the relation using the coin code as key.

SELECT i.user, i.buyingPrice, i.amount * c.price as value
FROM investments i
JOIN currencies c
ON i.coin = c.code

Combine investments into crypto and NFTs

To combine both types of investments into one query, we use to UNION keyword. Note how we use column mapping to change the name of field owner to user to obtain correspondingly named columns.

SELECT i.user, i.buyingPrice, i.amount * c.price as value
FROM investments i
JOIN currencies c
ON i.coin = c.code
UNION
SELECT owner as user, buyingPrice, value FROM NFT

Aggregated per-user data

Our query returns a long list of investments. It would be useful for us to look at aggregated per-user data. We use the GROUP BY keyword to achieve this.

SELECT user, SUM(value) AS value, SUM(buyingPrice) AS buyingPrice
FROM (
  SELECT i.user, i.buyingPrice, i.amount * c.price as value
  FROM investments i
  JOIN currencies c
  ON i.coin = c.code
  UNION
  SELECT owner as user, buyingPrice, value FROM NFT
) GROUP BY user

Assigning user names

In the following step, we join our subquery on the collection users to replace the IDs with human friendly names. We will also use a dynamic expression to calculate the overall profit of the user.

SELECT u.name, c.value - c.buyingPrice AS profit
FROM (
  SELECT user, SUM(value) AS value, SUM(buyingPrice) AS buyingPrice
  FROM (
    SELECT i.user, i.buyingPrice, i.amount * c.price as value
    FROM investments i
    JOIN currencies c
    ON i.coin = c.code
    UNION
    SELECT owner as user, buyingPrice, value FROM NFT
  ) GROUP BY user
) c
JOIN users u
ON c.user = u._documentId

Ordering by profit

In the last step, we will add descending ordering by profit to see the best investors on top of our list. Because profit is a dynamic value calculated from an expression, we have to wrap the whole query once again in a subselect to use the ORDER BY keyword.

Our final query looks like this:

SELECT * FROM (
  SELECT u.name, c.value - c.buyingPrice AS profit
  FROM (
    SELECT user, SUM(value) AS value, SUM(buyingPrice) AS buyingPrice
    FROM (
      SELECT i.user, i.buyingPrice, i.amount * c.price as value
      FROM investments i
      JOIN currencies c
      ON i.coin = c.code
      UNION
      SELECT owner as user, buyingPrice, value FROM NFT
    ) GROUP BY user
  ) c
  JOIN users u
  ON c.user = u._documentId
) ORDER BY profit DESC

After running the query, it gives us following results:

Calling as a stored view

This is a long complex query. Our business analysts probably don’t want to have to deal with all this code, they only care for the final report. We will save our query as a stored view cryptoProfitsReport. The report can then be called with a one-liner:

SELECT * FROM cryptoProfitsReport

Is this a good database design?

NoSQL databases require a different way of thinking and different database design than relational databases. Logically, a question arises: is this a good database design? Our goal as data engineers is to create a efficient and reliable database. Using relations between collections is not very efficient and we should try to avoid it when possible. On the other hand, NoSQL databases provide more options to substructure data.

It would be probably better to have the collections investments and NFT nested inside respective user documents as these are modified rarely only on creating, updating or deleting investments.

On the other hand, keeping crypto prices in a separate collection and looking them up when necessary is probably a good decision, as these get updated very often. An alternative would be to have the current values stored in the investment documents, this would however mean having to recalculate them every time the price changes, which could occur multiple times in a minute.

What's next?

Now it’s time for you to take on your business’ needs. Jetspike provides you with all the tools necessary to design high value reports and gain insights into your data. We hope that this example inspired you to solve your unique problems.

To the app