Snowflake's data-sharing operations team wants to identify shares containing stale required tables. Use the latest refresh record for each shared table that occurred at or before March 1, 2025 at midnight. A required table is considered fresh when its latest refresh was successful and occurred within the previous 24 hours. Return only data shares where at least one required table is stale or missing a valid refresh. Return the columns in this exact order: `share_id`, `share_name`, `account_name`, `required_tables`, `fresh_tables`, and `freshness_rate`. Calculate `freshness_rate` as fresh required tables divided by total required tables, rounded to four decimal places. Ignore optional tables. Sort by `freshness_rate` descending, then `share_id` descending. ### Table Schema | Table | Column | Type | |---|---|---| | `accounts` | `account_id` | integer | | `accounts` | `account_name` | varchar(80) | | `data_shares` | `share_id` | integer | | `data_shares` | `share_name` | varchar(80) | | `data_shares` | `account_id` | integer | | `share_tables` | `share_id` | integer | | `share_tables` | `table_id` | integer | | `share_tables` | `is_required` | boolean | | `table_refreshes` | `refresh_id` | integer | | `table_refreshes` | `table_id` | integer | | `table_refreshes` | `refreshed_at` | timestamp | | `table_refreshes` | `refresh_status` | varchar(20) | ### Example Input: `accounts` | account_id | account_name | |---:|---| | 1 | Solstice Analytics | | 2 | Northstar Labs | ### Example Input: `data_shares` | share_id | share_name | account_id | |---:|---|---:| | 101 | Customer Metrics | 1 | | 102 | Operations Feed | 2 | ### Example Input: `share_tables` | share_id | table_id | is_required | |---:|---:|---| | 101 | 1001 | true | | 101 | 1002 | true | | 101 | 1003 | false | | 102 | 1004 | true | | 102 | 1005 | true | ### Example Input: `table_refreshes` | refresh_id | table_id | refreshed_at | refresh_status | |---:|---:|---|---| | 1 | 1001 | 2025-02-28 12:00:00 | success | | 2 | 1002 | 2025-02-27 20:00:00 | success | | 3 | 1004 | 2025-02-28 18:00:00 | success | | 4 | 1005 | 2025-03-01 00:00:00 | failed | ### Example Output Explanation Customer Metrics has two required tables, but only table 1001 refreshed successfully within the previous 24 hours (table 1002's refresh happened more than 24 hours before the cutoff). Its freshness rate is 0.5000. Operations Feed has one fresh required table and one failed required table, so its freshness rate is also 0.5000. Optional table 1003 is ignored.
DROP TABLE IF EXISTS table_refreshes; DROP TABLE IF EXISTS share_tables; DROP TABLE IF EXISTS data_shares; DROP TABLE IF EXISTS accounts; CREATE TABLE accounts ( account_id INT, account_name VARCHAR(80) ); CREATE TABLE data_shares ( share_id INT, share_name VARCHAR(80), account_id INT ); CREATE TABLE share_tables ( share_id INT, table_id INT, is_required BOOLEAN ); CREATE TABLE table_refreshes ( refresh_id INT, table_id INT, refreshed_at TIMESTAMP, refresh_status VARCHAR(20) ); INSERT INTO accounts VALUES (1,'Solstice Analytics'), (2,'Northstar Labs'); INSERT INTO data_shares VALUES (101,'Customer Metrics',1), (102,'Operations Feed',2); INSERT INTO share_tables VALUES (101,1001,TRUE), (101,1002,TRUE), (101,1003,FALSE), (102,1004,TRUE), (102,1005,TRUE); INSERT INTO table_refreshes VALUES (1,1001,'2025-02-28 12:00:00','success'), (2,1002,'2025-02-27 20:00:00','success'), (3,1004,'2025-02-28 18:00:00','success'), (4,1005,'2025-03-01 00:00:00','failed');