A subquery is a query that appears inside another query statement. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
In Bright Analytics you will have the flexibility to build these advanced datasource types by following the steps below:
To start, go to the 'datasources' tab in the Report Engine or Dashboard App.
Click on the folder where you want to create the new SubQuery Datasource and click on the vertical three dots (2). From the options presented, click on 'Create Subquery' (3).
A pop-up will appear asking to select a table and give a Subquery Datasource name. The tables in the dropdown list will be the parent datasource table. In the example below, we have selected linkedin_data_followers (1) as the parent table and set the free text datasource name to linkedin_data_subquery (2). Finally, click on the green “Create Subquery Datasource” button.
Once the new subquery datasource has been created you will see a new datasource in the selected folder with the name that was set in the free text field and a different icon to how the traditional tables have in the Report Engine, this can be seen below
Once the subquery has been created (1) click on the 'subquery' tab (2).
In this tab you should see a free text field (1) where you can add your SubQuery. Once it has been added go to “Review and Save” (2).
In the “Review and Save” pop-up go to “Save changes” to apply the new SubQuery to the data model.
Once the SubQuery has been successfully saved, navigate back to the “Fields” tab (1) and click the button “Add all new fields” (2) and once again, “Review and Save” (3) followed by “Save changes” in the pop-up modal.
Add the new fields and updating them is essential because these will later be used to create metrics and dimensions.
After that you should be able to see the field IDs on the left-hand side of the column names. You can now go ahead and create your dimensions and metrics for the newly created subquery datasource.