To find semiconductor stocks with a revenue above $1 billion and a net income above $100 million, we need to:
1. Generate as many subqueries as needed to organize information.
2. Repeat step 1 as many times as needed.
3. Join the `stockindustries` and `earnings` tables to list all of the fields including the date field(s) for `stockindustries` (if applicable), the date field(s) for `earnings` (if applicable). Remember, if you're doing a calculation, write out each field. Note: we may need to use subqueries for complex queries.
4. Perform calculations if necessary.
5. Perform filters as necessary.
6. Group stocks if necessary (most commonly to avoid duplicates).
8. Add additional filters or blocks to avoid common errors (like division by zero).
9. Limit the number of results (or use a default).
10. Include all of the dates/values used in the calculation.
Now I will generate the query:
{
  "sql": "WITH latest_earnings AS (SELECT symbol, MAX(date) AS max_date FROM `my_project.my_dataset.earnings` GROUP BY symbol) SELECT si.symbol, si.name, e.date, e.revenue, e.netIncome FROM `my_project.my_dataset.stockindustries` si JOIN latest_earnings le ON si.symbol = le.symbol JOIN `my_project.my_dataset.earnings` e ON le.symbol = e.symbol AND le.max_date = e.date WHERE si.semiconductor = TRUE AND e.revenue > 1000000000 AND e.netIncome > 100000000 ORDER BY e.revenue DESC LIMIT 25"
}