Looking for SQL experts to help write queries

Looking for SQL experts to help write queries


Job Description


We are looking for SQL experts to help us write queries for a data application.

As part of your job application, please send over the text for a a query that performs the following simple task.

(Consider this easy task to be the first part of the interview...if you can't do this within 5min, you're not the right person for the job)

Two tables, A and B.

Table A is the trading signal table, containing trading signals (Buy / Sell / Hold) for various dates. Table A contains 3 columns: date, signal, and topic_id.

Table B is the category table, containing 2 columns, topic_id and category

We need to create a pivot table, based on the data in table A.

Pivot table columns will be Date, Buy, Sell, Hold.

For the first column, list the dates. For the remaining columns, you need to display the count of observations.

One requirement: Only do the pivot table for table A topic_ids that are part of category 1, as described in table B. In other words, you have to filter the table A pivot table by using the values found in table B.