Get SQL metadata (first level lineage) for most type of sql queries with inner sub-queries and other complex joins
SELECT investments.month_nm AS month_nm,
acquisitions.companies_acquired,
investments.companies_rec_investment
FROM (
SELECT acq.acquired_month_nm AS month_nm,
COUNT(DISTINCT acq.company_permalink) AS companies_acquired
FROM tutorial.crunchbase_acquisitions acq
GROUP BY 1
) acquisitions
FULL JOIN (
SELECT invst.funded_month_nm AS month_nm,
COUNT(DISTINCT invst.company_permalink) AS companies_rec_investment
FROM tutorial.crunchbase_investments invst
GROUP BY 1
) investments
ON acquisitions.month_nm = investments.month_nm
**** Database.Table alias mapping ****
Subquery mapping alias: acquisitions acq -> tutorial.crunchbase_acquisitions Subquery mapping alias: investments invst -> tutorial.crunchbase_investments
**** Column, Database and Table mapping ****
month_nm -> tutorial.crunchbase_investments.funded_month_nm companies_acquired -> count(DISTINCT tutorial.crunchbase_acquisitions.company_permalink) companies_rec_investment -> count(DISTINCT tutorial.crunchbase_investments.company_permalink)
{'acquisitions': {'acq': 'tutorial.crunchbase_acquisitions'}, 'investments': {'invst': 'tutorial.crunchbase_investments'}}
{'month_nm': 'tutorial.crunchbase_investments.funded_month_nm', 'companies_acquired': 'count(DISTINCT tutorial.crunchbase_acquisitions.company_permalink)', 'companies_rec_investment': 'count(DISTINCT tutorial.crunchbase_investments.company_permalink)'}
SELECT investments.month_nm AS month_nm,
acquisitions.companies_acquired,
investments.companies_rec_investment
FROM (
SELECT acq.acquired_month_nm AS month_nm,
COUNT(DISTINCT acq.company_permalink) AS companies_acquired
FROM tutorial.crunchbase_acquisitions acq
GROUP BY 1
) acquisitions
FULL JOIN (
SELECT invst.funded_month_nm AS month_nm,
COUNT(DISTINCT invst.company_permalink) AS companies_rec_investment
FROM tutorial.crunchbase_investments invst
GROUP BY 1
) investments
ON acquisitions.month_nm = investments.month_nm