My strong believe is that the most important part of the job interview for a BI position is a practical SQL task. From my experience, it is a good predictor for future performance. I prefer giving a relatively complex task that can be solved within 15 minutes. To minimize the stress factor, I usually give the task in the end of the interview and ask to send the solution within 1 hour.
The database table REVENUE contains revenue amount obtained at certain month. It has the following fields: YEAR, MONTH, AMOUNT. For the current date, calculate YTD value and estimated full year value under two assumptions: 1) even distribution of the revenue, 2) seasonal distribution of the revenue.
The database table PROJECT contains the information about projects, project start and end date (inclusive) and the total budget for the project duration. It has the following fields: PROJECTID, STARTDATE, ENDDATE, BUDGET. Distribute the budget to months evenly by day. The output should contain fields PROJECTID, YEAR, MONTH, BUDGET. You can use an auxiliary table CALENDAR with the fields THEDATE, YEAR, MONTH. You can assume it covers all dates used in the table PROJECT.
(I am going to add more sample tasks here)