SELECT linkResult.url AS url, linkResult.title AS title,
       (SELECT MIN(r.linkQuery.queryDate.date)
           FROM LinkResult r
             WHERE r.linkQuery.domain=linkQuery.domain
                AND r.url=linkResult.url) AS indexedDate
FROM CompetitiveEntity entity,
      IN(entity.domains) domain,
      LinkQuery linkQuery,
      IN(linkQuery.results) linkResult
   LEFT JOIN FETCH linkResult.url.hostname
     WHERE entity.id=2062
       AND (linkResult.url.hostname NOT MEMBER entity.domains)
       AND (linkQuery.domain=domain)
       AND (linkQuery.id=(SELECT MAX(lastQuery.id)
                    FROM LinkQuery lastQuery
                       WHERE lastQuery.domain=domain
                    AND SIZE(lastQuery.results) > 0 ) )
GROUP BY linkResult.url
ORDER BY linkResult.url

SELECT subscription.term.id, subscription, termQueryPrev.provider.name,
       MIN(termResultPrev.position) as prevPosition, MIN(termResultCurrent.position) as currentPosition,
       MIN(cast(termResultCurrent.position as integer)) - MIN(cast(termResultPrev.position as integer)) as change
  FROM TermSubscription subscription, 
       TermQuery termQueryPrev,
       IN(termQueryPrev.results) termResultPrev,
       TermQuery termQueryCurrent,
       IN(termQueryCurrent.results) termResultCurrent
  WHERE subscription.account.id= :accountId 
   AND termQueryPrev.term=subscription.term
   AND termQueryCurrent.term=subscription.term
   AND termQueryCurrent.provider.id=termQueryPrev.provider.id
   AND termQueryPrev.queryDate.yearWeek = YEARWEEK(FROM_DAYS(TO_DAYS(current_date) - 7), 1)
   AND termQueryCurrent.queryDate.yearWeek = YEARWEEK(:endDate,1)
   AND termResultPrev.url.hostname   MEMBER subscription.account.domains
   AND termResultCurrent.url.hostname   MEMBER subscription.account.domains 
GROUP BY subscription.term.id, termQueryPrev.queryDate.yearWeek,termQueryPrev.provider.id
HAVING MIN(cast(termResultCurrent.position as integer)) != MIN(cast(termResultPrev.position as integer))
ORDER BY ABS(MIN(cast(termResultCurrent.position as integer)) - MIN(cast(termResultPrev.position as integer))) DESC