Advanced example
See how you can use Jetspike to enable complex SQL reporting on Firestore and solve your business' problems
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.