Sub-queries in KQL Kibana

I am using ELK stack to perform some analysis on my data. My index pattern has 2 types of data

  1. The ID of the product clicked
  2. The ID of the product sold

I would like to first find all the IDs sold out and based on that find the number of times that ID was clicked.
Example-
1, clicked
1, sold
2, clicked
2, clicked
2, sold
3. clicked

So in this data-> ID1 and ID2 were sold. And ID1 was clicked once and ID2 was clicked twice.

I am unable to achieve this in KQL.

You can achieve this by using filter aggregations in a visualization.
Either in Lens or even in normal Bar Chart.
You do a terms Aggregation on the product ID, with a count metric. Then you can split that aggregation with a filter one where the first filter is "action: clicked" and the second one is "action: sold".

This will give me 2 Aggregations one for 'sold' and the other one for 'clicked'.
I would like to get the Number of clicks for all the Sold Items only. When I apply a filter on 'sold', I get only sold rows. Now how can I get the data for the click? Its similar to sub-queries in SQL.

I checked many posts, and all say that Sub-Queries in ES is not possible. Moreover, if I start using "Canvas" and "SQL", it does not support all the Sub-Queries.
As of now, it seems like I will have to store all the data on a database (MySql or MongoDB) and use Grafana for Visualization.....