drop_partition in a script

Hello,

I'd like to use the drop_partition in an sql request where I retrieve partition id from the partitions table. I cannot manage to make it. Something like:

WITH partition_to_be_deleted AS  (SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='TEST' AND PROJECTION_NAME LIKE 'MY_TABLE%' AND PARTITION_KEY < ....))
SELECT DROP_PARTITION('TEST1.MY_TABLE', partition_nb)WHERE partition_nb IN (SELECT PARTITION_KEY FROM partition_to_be_deleted);

Is it possible?

Tx