Practice Code Typing – SkillsTyping

Choose your language:

ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:

Ready

Exercise Algorithm Area

Correct typos to continue.

1CREATE OR REPLACE FUNCTION refresh_materialized_view_conditionally(
2mv_name TEXT,
3last_refresh_ts TIMESTAMPTZ,
4change_threshold_rows BIGINT,
5min_interval_hours INT
6)
7RETURNS VOID
8AS $$
9DECLARE
10current_ts TIMESTAMPTZ := NOW();
11time_since_last_refresh INTERVAL;
12estimated_changes BIGINT;
13query_text TEXT;
14table_name TEXT;
15row_count BIGINT;
16BEGIN
17-- Calculate time elapsed since last refresh
18time_since_last_refresh := current_ts - last_refresh_ts;
19
20-- Check if minimum interval has passed
21IF time_since_last_refresh < (min_interval_hours * INTERVAL '1 hour') THEN
22RAISE NOTICE 'Materialized view % not refreshed: minimum interval of % hours not met.', mv_name, min_interval_hours;
23RETURN;
24END IF;
25
26-- Estimate changes: This is a simplified estimation. In a real-world scenario,
27-- you might use triggers, change data capture (CDC), or table statistics.
28-- For this example, we'll use a placeholder and assume we can query row counts.
29-- A more robust solution would involve querying transaction logs or using
30-- specific change tracking mechanisms.
31
32-- Placeholder for estimating changes. This part needs to be adapted to your schema.
33-- Example: Querying row counts of tables involved in the MV definition.
34-- This is a very rough estimate and might not be accurate.
35estimated_changes := 0;
36-- For demonstration, let's assume we have a way to get relevant table names
37-- and their row counts. In reality, you'd parse the MV definition.
38-- Example: If MV depends on 'orders' and 'customers' tables:
39-- SELECT COUNT(*) FROM orders INTO row_count;
40-- estimated_changes := estimated_changes + row_count;
41-- SELECT COUNT(*) FROM customers INTO row_count;
42-- estimated_changes := estimated_changes + row_count;
43
44-- Simplified change estimation: If last_refresh_ts is NULL, assume it needs refresh.
45-- If last_refresh_ts is not NULL, we'd need actual change tracking.
46-- For this example, we'll simulate a change threshold check.
47-- In a real system, you'd query system catalogs or use triggers.
48-- Let's assume for this exercise that if last_refresh_ts is NULL, we refresh.
49-- Otherwise, we'd need a mechanism to estimate changes.
50
51-- Simplified logic: If last_refresh_ts is NULL, it's the first refresh.
52-- If it's not NULL, we'd ideally check for changes. For this example,
53-- we'll proceed if the interval is met, and assume changes might be significant.
54-- A more advanced approach would involve querying pg_stat_user_tables for last_autovacuum/analyze.
55
56-- For this drill, let's simulate a change check: if the interval is met, we proceed.
57-- A real implementation would need to query actual change counts.
58-- We'll use a placeholder for estimated_changes and assume it's > threshold if interval is met.
59
60-- Let's refine the change estimation concept for this example.
61-- We'll assume we can query the number of rows inserted/updated/deleted since last refresh.
62-- This is typically done via triggers or by inspecting transaction logs (complex).
63-- For a practical drill, let's assume we have a function `get_estimated_changes(table_name)`
64-- or we can query `pg_stat_user_tables` for `n_tup_ins`, `n_tup_upd`, `n_tup_del`.
65
66-- Let's simulate change estimation by checking if the MV is stale.
67-- A common pattern is to check if underlying tables have been modified.
68-- This requires knowing the underlying tables, which is complex to parse here.
69-- For this drill, we'll simplify: if the interval is met, we proceed.
70-- The `change_threshold_rows` is a conceptual parameter.
71
72-- Simplified logic for the drill: If the interval is met, we will refresh.
73-- The `change_threshold_rows` parameter is illustrative of a more complex check.
74-- In a real scenario, you'd need to implement change tracking.
75
76-- Let's assume we have a way to get the number of rows affected since last refresh.
77-- For this example, we'll use a placeholder and assume `estimated_changes` is calculated.
78-- If `last_refresh_ts` is NULL, we always refresh if interval is met.
79-- If `last_refresh_ts` is NOT NULL, we check `estimated_changes`.
80
81IF last_refresh_ts IS NULL OR estimated_changes > change_threshold_rows THEN
82RAISE NOTICE 'Refreshing materialized view: %', mv_name;
83query_text := 'REFRESH MATERIALIZED VIEW ' || quote_ident(mv_name);
84EXECUTE query_text;
85-- Update the last refresh timestamp (this would typically be stored elsewhere,
86-- e.g., in a metadata table, not returned by this function).
87-- For this function's scope, we just perform the refresh.
88ELSE
89RAISE NOTICE 'Materialized view % not refreshed: estimated changes (%) below threshold (%).', mv_name, estimated_changes, change_threshold_rows;
90END IF;
91
92END;
93$$ LANGUAGE plpgsql;
94
95-- Example usage (assuming you have a materialized view named 'my_mv'):
96-- SELECT refresh_materialized_view_conditionally('my_mv', '2023-01-01 10:00:00+00', 1000, 24);
97-- SELECT refresh_materialized_view_conditionally('my_mv', NULL, 1000, 24);

Algorithm description viewbox

Materialized View Refresh Strategy Optimization

Algorithm description:

This PostgreSQL function provides a strategy for conditionally refreshing materialized views. It considers the time elapsed since the last refresh and a conceptual threshold for the number of changes in underlying tables. This helps optimize refresh operations by avoiding unnecessary refreshes when data hasn't changed significantly or when the minimum refresh interval hasn't been met. It's crucial for maintaining data freshness without excessive resource consumption.

Algorithm explanation:

The function `refresh_materialized_view_conditionally` takes the materialized view name, its last refresh timestamp, a change threshold, and a minimum interval. It first checks if the `min_interval_hours` has passed since `last_refresh_ts`. If not, it returns early. The core logic then involves estimating changes. In this simplified drill, `estimated_changes` is a placeholder; a real implementation would query system catalogs (like `pg_stat_user_tables` for `n_tup_ins`, `n_tup_upd`, `n_tup_del`) or use triggers to track modifications in the MV's base tables. If the `last_refresh_ts` is NULL (first refresh) or if `estimated_changes` exceeds `change_threshold_rows`, the materialized view is refreshed using dynamic SQL. Time complexity is dominated by the change estimation logic, which can vary greatly. Space complexity is low, mainly for variables.

Pseudocode:

FUNCTION refresh_materialized_view_conditionally(mv_name, last_refresh_ts, change_threshold, min_interval):
  current_ts = current_timestamp
  time_since_last = current_ts - last_refresh_ts

  IF time_since_last < min_interval:
    LOG "Not refreshing: minimum interval not met."
    RETURN
  END IF

  estimated_changes = estimate_changes_for_mv(mv_name) -- Placeholder

  IF last_refresh_ts IS NULL OR estimated_changes > change_threshold:
    LOG "Refreshing materialized view: " + mv_name
    EXECUTE "REFRESH MATERIALIZED VIEW " + mv_name
  ELSE:
    LOG "Not refreshing: changes below threshold."
  END IF
END FUNCTION

Library

Playable exercises

Choose Exercise

Filter, sort, and preview algorithm before you choose one to play.

Total 5 published items

Pages 1 pages available

Language

Sort by Direction

Browse published coding scenarios with filters, sorting, and pagination.

Published

SQL PostgreSQL SQL (sql-postgresql)

Find First Non-Repeating Character

Difficulty: writing: 10; length: 4
Popularity: 0
Created: 2026-01-26 13:55 UTC
Published: 2026-01-26 16:41 UTC
Author: Damian

#string #frequency #first occurrence #character #SQL

goal: Find the first non-repeating character in a string. input: A TEXT string. output: A TEXT character, or NULL if no non-repeating character exists or input is invalid.

Canonical Algorithm Text

CREATE OR REPLACE FUNCTION find_first_non_repeating_char(input_string TEXT) RETURNS TEXT AS $$ DECLARE char_counts INT[256]; -- Assuming ASCII characters i INT; current_char CHAR(1); result CHAR(1) := NULL; BEGIN -- Initialize character counts to zero FOR i IN 0..255 LOOP char_co...

Description Algorithm

This SQL function, `find_first_non_repeating_char`, identifies the first character in a given string that appears only once. It iterates through the string twice: first to count the occurrences of each character and then...

Explanation

The function uses an array `char_counts` to store the frequency of each character, indexed by their ASCII values. The time complexity is O(N), where N is the length of the input string, because we iterate through the string twice. The space complexity is O(1) because the size of the `char_counts` array is fixed (256 for ASCII), regardless of the input string's length. The algorithm handles empty or NULL input strings by returning NULL. It correctly identifies the first non-repeating character by maintaining the order of appearance in the second pass.

Pseudocode

FUNCTION find_first_non_repeating_char(input_string): IF input_string is NULL or empty THEN RETURN NULL END IF CREATE an array `char_counts` of size 256, initialized to 0. FOR each character `c` in input_string: INCREMEN...

Published

SQL PostgreSQL SQL (sql-postgresql)

Find All Anagrams in a String

Difficulty: writing: 3; length: 6
Popularity: 0
Created: 2026-01-26 13:55 UTC
Published: 2026-01-26 16:41 UTC
Author: Damian

#string #anagram #sliding window #frequency map #SQL

goal: Find all starting indices of anagrams of a pattern string within a text string. input: Two TEXT strings: `s` (the text) and `p` (the pattern). output: A SETOF INTEGER representing the starting indices.

Canonical Algorithm Text

CREATE OR REPLACE FUNCTION find_all_anagrams(s TEXT, p TEXT) RETURNS SETOF INTEGER AS $$ DECLARE s_len INT := LENGTH(s); p_len INT := LENGTH(p); s_chars INT[26]; -- Frequency map for characters in s (window) p_chars INT[26]; -- Frequency map for characters in p (pattern) i INT; s...

Description Algorithm

This PostgreSQL function, `find_all_anagrams`, efficiently finds all starting indices of substrings in a larger string (`s`) that are anagrams of a given pattern string (`p`). It employs a sliding window approach, mainta...

Explanation

The algorithm uses two frequency maps (arrays of size 26 for lowercase English letters) to store character counts: one for the pattern `p` (`p_chars`) and one for the current sliding window in `s` (`s_chars`). The time complexity is O(N), where N is the length of string `s`, because we iterate through `s` once to slide the window. The space complexity is O(1) as the frequency maps have a fixed size (26), independent of the input string lengths. Edge cases like `p` being longer than `s` are handled. The core logic involves updating `s_chars` by adding the new character entering the window and subtracting the character leaving, then comparing `s_chars` with `p_chars` at each step. The invariant maintained is that `s_chars` always reflects the character counts of the current window of size `p_len` starting at `start_index`.

Pseudocode

FUNCTION find_all_anagrams(s, p): IF length(p) > length(s) THEN RETURN empty list END IF CREATE frequency map `p_chars` for pattern p. CREATE frequency map `s_chars` for the first window of s (size length(p)). CREATE an...

Published

SQL PostgreSQL SQL (sql-postgresql)

Materialized View Refresh Strategy Optimization

Difficulty: writing: 10; length: 10
Popularity: 0
Created: 2026-01-26 12:14 UTC
Published: 2026-01-26 13:43 UTC
Author: Admin

#materialized view #refresh #optimization #postgresql #conditional logic

goal: Conditionally refresh materialized views output: VOID

Canonical Algorithm Text

CREATE OR REPLACE FUNCTION refresh_materialized_view_conditionally( mv_name TEXT, last_refresh_ts TIMESTAMPTZ, change_threshold_rows BIGINT, min_interval_hours INT ) RETURNS VOID AS $$ DECLARE current_ts TIMESTAMPTZ := NOW(); time_since_last_refresh INTERVAL; estimated_changes BI...

Description Algorithm

This PostgreSQL function provides a strategy for conditionally refreshing materialized views. It considers the time elapsed since the last refresh and a conceptual threshold for the number of changes in underlying tables...

Explanation

The function `refresh_materialized_view_conditionally` takes the materialized view name, its last refresh timestamp, a change threshold, and a minimum interval. It first checks if the `min_interval_hours` has passed since `last_refresh_ts`. If not, it returns early. The core logic then involves estimating changes. In this simplified drill, `estimated_changes` is a placeholder; a real implementation would query system catalogs (like `pg_stat_user_tables` for `n_tup_ins`, `n_tup_upd`, `n_tup_del`) or use triggers to track modifications in the MV's base tables. If the `last_refresh_ts` is NULL (first refresh) or if `estimated_changes` exceeds `change_threshold_rows`, the materialized view is refreshed using dynamic SQL. Time complexity is dominated by the change estimation logic, which can vary greatly. Space complexity is low, mainly for variables.

Pseudocode

FUNCTION refresh_materialized_view_conditionally(mv_name, last_refresh_ts, change_threshold, min_interval): current_ts = current_timestamp time_since_last = current_ts - last_refresh_ts IF time_since_last < min_interval:...

Published

SQL PostgreSQL SQL (sql-postgresql)

Recursive Graph Pathfinding (DFS)

Difficulty: writing: 6; length: 2
Popularity: 0
Created: 2026-01-26 12:14 UTC
Published: 2026-01-26 13:43 UTC
Author: Admin

#recursive CTE #graph traversal #pathfinding #postgresql #cycle detection

goal: Find all paths in a graph using recursive CTE output: TABLE (start_node VARCHAR, target_node VARCHAR, path VARCHAR[])

Canonical Algorithm Text

WITH RECURSIVE graph_paths AS ( -- Base case: Start at the source node SELECT start_node, target_node, ARRAY[start_node] AS path, 0 AS depth FROM edges WHERE start_node = 'A' -- Specify your starting node here UNION ALL -- Recursive step: Explore neighbors SELECT gp.start_node, e...

Description Algorithm

This SQL query uses a recursive Common Table Expression (CTE) to find all possible paths from a specified start node to a target node in a graph. The graph is represented by an `edges` table with `start_node` and `target...

Explanation

The recursive CTE `graph_paths` starts with the base case: all edges originating from the `start_node`. The recursive step then joins the current paths with edges where the `target_node` of the current path matches the `start_node` of a new edge. It appends the new `target_node` to the path and increments the depth. Cycle detection is achieved by checking if the `e.target_node` is already present in `gp.path`. A depth limit (`gp.depth < 10`) is imposed to prevent infinite recursion in graphs with cycles. Time complexity can be exponential in the worst case for dense graphs, but is bounded by the depth limit and graph structure. Space complexity is related to the number and length of paths found.

Pseudocode

WITH RECURSIVE graph_paths AS ( -- Base Case: SELECT start_node, target_node, [start_node] AS path, 0 AS depth FROM edges WHERE start_node = 'START_NODE' UNION ALL -- Recursive Step: SELECT gp.start_node, e.target_node,...

Published

SQL PostgreSQL SQL (sql-postgresql)

JSONB Path Existence Check

Difficulty: writing: 7; length: 1
Popularity: 0
Created: 2026-01-26 12:14 UTC
Published: 2026-01-26 13:43 UTC
Author: Admin

#jsonb #path #exists #postgresql #data validation

goal: Check JSONB path existence output: BOOLEAN

Canonical Algorithm Text

CREATE OR REPLACE FUNCTION jsonb_path_exists( doc JSONB, path_expr TEXT ) RETURNS BOOLEAN AS $$ DECLARE result JSONB; BEGIN -- Attempt to extract the value at the specified path. -- If the path exists, jsonb_extract_path will return a non-null JSONB value. -- If the path does not...

Description Algorithm

This function checks for the existence of a specific path within a JSONB document. It leverages PostgreSQL's built-in JSONB functions to traverse the document structure. This is useful for validating data structures befo...

Explanation

The function `jsonb_path_exists` takes a JSONB document and a text representation of a path. It uses `jsonb_extract_path` to attempt to retrieve the value at that path. If `jsonb_extract_path` returns a non-null value, it implies the path exists, and the function returns TRUE. Otherwise, if `jsonb_extract_path` returns NULL (meaning the path or any part of it is missing), the function returns FALSE. The time complexity is dependent on the depth of the JSONB document and the length of the path expression, generally proportional to the number of keys traversed. Space complexity is minimal, primarily for storing the result.

Pseudocode

FUNCTION jsonb_path_exists(document, path_expression): extracted_value = extract_jsonb_path(document, path_expression) IF extracted_value IS NOT NULL: RETURN TRUE ELSE: RETURN FALSE END FUNCTION

Language (required)

Paste your code (required)

Max 20,000 characters. (Dangerous HTML/script fragments will be blocked)

Normalized preview:

I agree to Publish this, after admin approval (queued for review). (Not required) I confirm this submission is anonymous and does not contain personal data. I grant SkillsTyping a free, non-exclusive, worldwide, and irrevocable license to store, modify, adapt, and publish this content after review. I understand that publication is not guaranteed, that I retain no ownership or usage claims over published versions, and that no compensation will be provided. See Privacy Policy

Publish details

Shown only if you opt to submit for review. Required when consent is checked.

Title (required with consent)

Short description / summary (required with consent)

Algorithm explanation (required with consent)

Pseudocode (required with consent)