My original answer is not valid anymore, now there's a good solution for this, see Sukhjinder Singh answer. In short: use Devel module and add tag 'debug', execute it and query will be printed.
As @kiamlaluno says, the problem is \Drupal::entityQuery() implements QueryInterface, not SelectInterface. dpq needs its first argument to implements that SelectInterface.
The object returned by \Drupal::entityQuery() is a Drupal\Core\Entity\Query\Sql\Query. This object has a protected property that implements the SelectInterface:
* The build sql select query.
* @var \Drupal\Core\Database\Query\SelectInterface
Unfortunately, the Query objects don't allow to access the $sqlQuery or print the SQL string.
As a very quick and dirty hack you can edit the Query class file in core/lib/Drupal/Core/Entity/Query/Sql/Query.php, change the $sqlQuery property to be public, and access it directly.
protected $sqlQuery should be
Then you can use the dpq function from devel module with the internal property. Make sure the devel module is installed (so it's enabled).
Prints a SQL string from a DBTNG Select object. Includes quoted
So in your case:
$sql_string = dpq($query->sqlQuery, TRUE));
object $query: An object that implements the SelectInterface interface.
boolean $return: Whether to return the string. Default is FALSE, meaning to print it and return $query instead.
string $name: Optional name for identifying the output.
This is a dirty trick, you should revert the Query.php file when you are done. Hacking Drupal core is a very bad practice and kittens, ponies and other puppies are suffering when you do that. Maybe even some unicorns are - better think twice about it.
This a dirty workaround. Another better approach may exist but I'm not aware of it. If there is not a better way Drupal core may add some functionality to get the SQL string as sometimes is needed when debugging, and frankly, this hack is not a good way to do it.