To do so, the actual search results and the sponsored ads are sorted respectively.
Then, room for the sponsored ads needs to be made in the sequence of the actual search results.
Finally, they are put together, and displayed in defined order.
You asked for every third job offer to be a sponsored one:
SearchTerm AS (
ROW_NUMBER() OVER (ORDER BY created_at) AS search_term_order
WHERE description LIKE '%programmer%'
PremiumAdd AS (
ROW_NUMBER() OVER (ORDER BY created_at DESC) AS premium_order
WHERE is_premium = 1
FinalOrder AS (
, MOD(search_term_order + 1, 2) + FLOOR((search_term_order + 1) / 2) * 3 - 2 AS position
-- , MOD(search_term_order + 2, 3) + FLOOR((search_term_order + 2) / 3) * 4 - 3 AS position
, premium_order * 3
-- , premium_order * 4
ORDER BY position
id, description, created_at, is_premium FROM FinalOrder
The comments show the adjustments for having an ad in every fourth row…
- Ads holding the search term would be listed twice. This could be handled by adjusting the
- This requires MySQL 8.
Please comment, if and as this requires adjustment / further detail.